1) Method 1: INSERT INTO SELECT

this method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are are not required to list them. I always list them for readability and scalability purpose.

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

-----------------------------------------------------------------

2) Method 2: SELECT INTO

This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.

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

---------------------------------------------------------------

Both of the above method works with database temporary tables (global, local). If you want to insert multiple rows using only one insert statement.