INSERT data from one table to another: insert into select/SELECT

Link: http://www.it118.org/Specials/c9fba99e-4401-49cf-8256-ac3c1a34c0d9/cf2a7ceb-1815-43fe-8810-47af11ac65a9.htm

Two methods can be implemented without using a cursor. The following two methods provide better performance than a cursor.

1. INSERT INTO SELECT
This method is usually used when the target table already exists in the Database. You need to insert data from the source table to the target table. If the target table and source table have the same columns, you do not need to list the data columns.

USE AdventureWorks 
GO 
----Create TestTable 
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100)) 
----INSERT INTO TestTable using SELECT 
INSERT INTO TestTable (FirstName, LastName) 
SELECT FirstName, LastName 
FROM Person.Contact 
WHERE EmailPromotion = 2 
----Verify that Data in TestTable 
SELECT FirstName, LastName 
FROM TestTable 
----Clean Up Database 
DROP TABLE TestTable 
GO 
USE AdventureWorks 
GO 
----Create TestTable 
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100)) 
----INSERT INTO TestTable using SELECT 
INSERT INTO TestTable --(FirstName, LastName) 
SELECT FirstName, LastName 
FROM Person.Contact 
WHERE EmailPromotion = 2 
----Verify that Data in TestTable 
SELECT FirstName, LastName 
FROM TestTable 
----Clean Up Database 
DROP TABLE TestTable 
GO 

Take a closer look at the differences.

2. SELECT
This method is used when the target table does not exist in the database. A new table is created when data is inserted. The field name and Data Type of the new table are the same as those of the columns to be inserted.

USE AdventureWorks 
GO 
----Create new table and insert into table using SELECT INSERT 
SELECT FirstName, LastName 
INTO TestTable 
FROM Person.Contact 
WHERE EmailPromotion = 2 
----Verify that Data in TestTable 
SELECT FirstName, LastName 
FROM TestTable 
----Clean Up Database 
DROP TABLE TestTable 
GO 

I also use it to copy data structures without inserting data.

USE AdventureWorks 
GO 
----Create new table and insert into table using SELECT INSERT 
SELECT FirstName, LastName 
INTO TestTable 
FROM Person.Contact 
WHERE 1=2 
DROP TABLE TestTable 
GO