SQL Server 'select * into' versus 'insert into ..select *

sql-server select-into
Sujit Prabhakaran picture Sujit Prabhakaran · Dec 19, 2011 · Viewed 88.9k times · Source

Say table1 and table2 already exist, is there any difference between these queries

query1 :-

select * into table1 from table2 where 1=1

query2: -

insert into table1 select * from table2

Answer

aF. picture aF. · Dec 19, 2011

The select * into table1 from table2 where 1=1 creates table1 and inserts the values of table2 in them. So, if the table is already created that statement would give an error.

The insert into table1 select * from table2 only inserts the values of table2 in table1.

Related questions

Preserving ORDER BY in SELECT INTO

I have a T-SQL query that takes data from one table and copies it into a new table but only rows meeting a certain condition: SELECT VibeFGEvents.* INTO VibeFGEventsAfterStudyStart FROM VibeFGEvents LEFT OUTER JOIN VibeFGEventsStudyStart ON CHARINDEX(REPLACE(REPLACE(REPLACE(…

Read More
There is already an object named 'tbltable1' in the database

I am trying to insert data from one table to another with same structure, select * into tbltable1 from tbltable1_Link I am getting the following error message: There is already an object named 'tbltable1' in the database.

Read More
SQL SELECT INTO query with a Join, Error "There is already an object named '*****' in the database."

I'm currently trying to copy data from table into another by using a SELECT INTO query. But Im receiving an error in SQL Server. "Msg 2714, Level 16, State 6, Line 2 There is already an object named 'Product' in the database." Im still …

Read More