CODEWITHSUNDEEP
sql-serversql-server-2008
SamJolly
SamJolly

Reputation: 6477

INSERT INTO SELECT * for SQL Server, Not possible, Am I correct?

Using SQL Server 2008 R2

Apparently one can do 

INSERT INTO Table1 SELECT * FROM Table1 where Id=1234

see: W3Schools

But in SQL Server this does not work. Am I correct in thinking that SQL Server does not support the above INSERT statement, and needs to specify the columns specifically.

Thanks.

EDIT

So incomplete SQL above, now corrected

Upvotes: 5

Views: 30861

Answers (2)

Tanner
Tanner

Reputation: 22733

If you're simply trying to copy a row from a table that has a primary key (IDENTITY column), then you can't use the following syntax:

INSERT INTO Table1 SELECT * FROM Table1 where Id=1234

This would violate the unique primary key values as it would try to insert a duplicate IDENTITY.

Instead, select all columns except the IDENTITY, assuming with this schema:

Table1
------
ID
Col1
Col2
Col3

You would do this and the IDENTITY would auto increment for you:

INSERT INTO Table1 (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM Table1 
WHERE Id=1234

Upvotes: 4

juergen d
juergen d

Reputation: 204746

You missed the name of the table you want to insert into

insert into destination_table
select * from source_table

Of course this works only for tables with equal columns. If you only want to insert specific columns then you need to specify them too

insert into destination_table (col1, col2)
select col7, col3 from source_table

Upvotes: 12

Related Questions