Menu
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 
772
INSERT INTO SELECT vs SELECT INTO: Both the statements could be used to copy data from one table to another. But INSERT INTO SELECT could be used only if the target table exists whereas SELECT INTO statement could be used even if the target table doesn't exist as it creates the target table if it doesn't exist.
INTO' creates the destination table, it exclusively owns that table and is quicker compared to the 'INSERT … SELECT'. Because the 'INSERT … SELECT' inserts data into an existing table, it is slower and requires more resources due to the higher number of logical reads and greater transaction log usage.
When you use select * you're make it impossible to profile, therefore you're not writing clear & straightforward code and you are going against the spirit of the quote. select * is an anti-pattern. So selecting columns is not a premature optimization.
The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match. Note: The existing records in the target table are unaffected.
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.
112
The first one (SELECT INTO) will create and populate a new table the second (INSERT... SELECT) inserts to an existing table.
In versions of SQL Server prior to 2008 the first one could be minimally logged and the second one not but this is no longer true.
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
