[dba-SQLServer] SELECT INTO question

Susan Harkins harkinsss at bellsouth.net
Mon Feb 27 17:06:42 CST 2006


Not INSERT INTO, SELECT INTO... 

SELECT INTO creates a new table:

SELECT * INTO newtable
FROM sourcetable

In Jet, if newtable exists, the statement writes over the existing instance
of newtable. In SQL Server Express, the statement fails with an error that
newtable already exists. I'm wondering which way SQL Server goes. 

Susan H. 

Sorry I read that wrong. In SQL 2000, if the correct data types are
selected, it will simply insert the values as new records:


INSERT INTO tblTest SELECT 'Test3'    (table tblTest only has one field, a
char(10) )


If the table does not exist, the table will be created (this is what I was
referring to in my last post).

If the table already exists but the number or columns are different or the
types don't match, then the user will get an error.

David


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of David McAfee
Sent: Monday, February 27, 2006 2:51 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] SELECT INTO question


SQL Server 2000 works like access. Some coworkers like using this method. I
don't.

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Susan
Harkins
Sent: Monday, February 27, 2006 2:40 PM
To: SQLList
Subject: [dba-SQLServer] SELECT INTO question


In Jet SQL, the SELECT INTO statement will overwrite an existing table if
the target table already exists. I just checked SQL Server Express and it
did NOT overwrite an existing table -- the statement failed with an error. I
uninstalled SQL Server awhile back, so I can't check it -- can anybody tell
me how SQL Server responds?

Susan H.

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
 




More information about the dba-SQLServer mailing list