Copy data from one table to another table is one of the most requested questions on forums, Facebook and Twitter. The question has come in many formats and there are places I have seen developers are using cursor instead of this direct method.
Earlier I have written the similar article a few years ago - SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE. The article has been very popular and I have received many interesting and constructive comments. However there were two specific comments keep on ending up on my mailbox.
1) SQL Server AdventureWorks Samples Database does not have table I used in the example
2) If there is a video tutorial of the same example.
After carefully thinking I decided to build a new set of the scripts for the example which are very similar to the old one as well video tutorial of the same. There was no better place than our SQL in Sixty Second Series to cover this interesting small concept.
Let me know what you think of this video. Here is the updated script.
-- Method 1 : INSERT INTO SELECT
USE AdventureWorks2012
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.Person
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO
---------------------------------------------------------
---------------------------------------------------------
-- Method 2 : SELECT INTO
USE AdventureWorks2012
GO
----Create new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Person
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO
Related Tips in SQL in Sixty Seconds:
SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE
Powershell – Importing CSV File Into Database – Video
SQL SERVER – 2005 – Export Data From SQL Server 2005 to Microsoft Excel Datasheet
SQL SERVER – Import CSV File into Database Table Using SSIS
SQL SERVER – Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server
SQL SERVER – 2005 – Generate Script with Data from Database – Database Publishing Wizard
What would you like to see in the next SQL in Sixty Seconds video?
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: Database, Pinal Dave, PostADay, SQL, SQL Authority, SQL in Sixty Seconds, SQL Query, SQL Scripts, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, T SQL, Technology, Video Tagged: Excel
