Monday, March 1, 2010

SQL Select Into & Insert Into Select

Here are two useful T-SQL query syntax for inserting select statements into tables:

SELECT table1.column1, table1.column2, table1.column3
INTO new_table
FROM table1

SELECT table2.column1, table2.column2, table2.column3
FROM table2

Both of these syntax inserts query results into a table, the only difference is that we use SELECT INTO to insert values from an existing table to a new one (a new table that is not yet present in the database).

And we use INSERT INTO SELECT to insert values from an existing table to another existing one. Of course be reminded that the number of select columns from the source table should match the destination table's columns. Also note that if you use SELECT INTO to copy values from an existing table to another one, you'll get an error like this: Msg 2714, Level 16, State 6 - There is already an object named 'table_name' in the database. So you really should use INSERT INTO SELECT in those cases.

No comments:

Post a Comment