SQL Server Insert Into Select Part 1


This post discusses how to insert data from one table into another existing table. The second table must already exist. If you want to take one table and insert data into a new table you would use the SQL command SELECT INTO. A couple of things to note when using the Insert Into Select command, as per w3schools.com.

  • INSERT INTO SELECT requires that data types in source and target tables match
  • The existing records in the target table are unaffected

Here is the syntax, according to w3schools.com.

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

If you want to copy only some columns from one table to another, here is the syntax according to w3schools.com.

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition

Order

What about the order? What if we want to specify a certain order of the source table and ensure that the order we specify is preserved in the target table? Can we do this? No. However, Part 3 of his series looks at the Insert Into Select For Xml statement. With the For Xml added, you can specify the order.

Leave a comment

Your email address will not be published. Required fields are marked *