INSERT INTO SELECT – SELECT INTO TABLE
Following three questions are many time asked on this blog.
How to insert data from one table to another table efficiently?
How to insert data from one table using where condition to anther table?
How can I stop using cursor to move data from one table to another table?
There are two different ways to implement inserting data from one table to another table. I strongly suggest to use either of the method over cursor. Performance of following two methods is far superior over cursor. I prefer to use Method 1 always as I works in all the case.
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.
<span style="color: #0000ff;">USE AdventureWorks</span>
<span style="color: #0000ff;"> GO</span>
<span style="color: #000000;"> ----Create TestTable</span>
<span style="color: #0000ff;"> CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))</span>
<span style="color: #000000;"> ----INSERT INTO TestTable using SELECT</span>
<span style="color: #0000ff;"> INSERT INTO TestTable (FirstName, LastName)</span>
<span style="color: #0000ff;"> SELECT FirstName, LastName</span>
<span style="color: #0000ff;"> FROM Person.Contact</span>
<span style="color: #0000ff;"> WHERE EmailPromotion = 2</span>
<span style="color: #000000;"> ----Verify that Data in TestTable</span>
<span style="color: #0000ff;"> SELECT FirstName, LastName</span>
<span style="color: #0000ff;"> FROM TestTable</span>
<span style="color: #0000ff;"> ----Clean Up Database</span>
<span style="color: #0000ff;"> DROP TABLE TestTable</span>
<span style="color: #0000ff;"> GO</span>
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.
<span style="color: #0000ff;">USE AdventureWorks</span>
<span style="color: #0000ff;"> GO</span>
<span style="color: #000000;"> ----Create new table and insert into table using SELECT INSERT</span>
<span style="color: #0000ff;"> SELECT FirstName, LastName</span>
<span style="color: #0000ff;"> INTO TestTable</span>
<span style="color: #0000ff;"> FROM Person.Contact</span>
<span style="color: #0000ff;"> WHERE EmailPromotion = 2</span>
<span style="color: #000000;"> ----Verify that Data in TestTable</span>
<span style="color: #0000ff;"> SELECT FirstName, LastName</span>
<span style="color: #0000ff;"> FROM TestTable</span>
<span style="color: #000000;"> ----Clean Up Database</span>
<span style="color: #0000ff;"> DROP TABLE TestTable</span>
<span style="color: #0000ff;"> GO</span>
Both of the above method works with database temporary tables (global, local). If you want to insert multiple rows using only one insert statement refer article SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
Home