Menu
I often see two styles, INSERT select and insert into select, what are the differences? Are they the same?
I am using SQL Server 2008 Enterprise.
Here are two samples.
INSERT california_authors (au_id, au_lname, au_fname)
SELECT au_id, au_lname, au_fname
FROM authors
WHERE State = 'CA'
http://www.sqlteam.com/article/using-select-to-insert-records
INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information
http://www.1keydata.com/sql/sqlinsert.html
thanks in advance, George
484
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.
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 Difference between SELECT INTO and INSERT INTO SELECT INTO creates a new table while INSERT INTO does NOT. For INSERT INTO, the table must exist else you need to create it.
In SQL Server and MySQL, INTO is pure syntax sugar.
No difference.
Oracle and PostgreSQL require this keyword to be present, and, AFAIR, ANSI SQL standards do too.
97
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
