SQL SERVER – Copy Data from One Table to Another Table – SQL in Sixty Seconds #031 – Video

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:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

16 thoughts on “SQL SERVER – Copy Data from One Table to Another Table – SQL in Sixty Seconds #031 – Video

  1. Hi, Pinal. This is a nice way to copy data from one table to another, but wouldn’t it create problems when the source and destination tables have identity turned on?

    I guess, we have to turn off the identity off on the destination table, copy the data and then turn on the identity on again.

    Is there an easy way to do this ?

    Thanks.

  2. Hi,

    I would like to meet those developers personaly who were using cursor to copy data from one table to another table.

    Thanks,
    Akash

  3. We have Sql server 2008 R2 and i would like to set up my new test server and copy production DB to new test Server

    We are taking back up – differential back up daily and during week end we take full back up.
    We have 4-6 DB on one server and couple of them kind of Data Warehousing (Dimensional and Fact Table).
    We don’t have that much sensitive data and we have only couple developers.
    Could you please let me know that i am taking correct steps?

    1) Will install Sql server 2008 R2 on new server
    2) Copy latest back up files for all the databases into new server
    3) Restored one by one database backup into new server
    4) Collect login script from source server and run into target server to handle orphan logins
    5) Run DBCC check command to verify the restore

  4. iamAkashSingh

    I would like to meet those developers personaly who were using cursor to copy data from one table to another table

    The best comment :))))

  5. Hi Pinal, can u help me out to get the query to copy table (WIth all constraints) to a diff scema with same tablename and in same db?

    …Sanjeeb

  6. Hi, Pinal, what if the table is large, I mean some million records? server hangs… “insufficient disk space” because the tempdb… how to deal with that? thanks

  7. Hi Pinal
    I want to insert data in table A from table B .
    Both the tables have unique column ID .
    Can I do this bulk insertion operation based on condition “Where A.ID=B.ID”.

  8. Pinal,

    I am always pleased to see your articles when they come up in searches. Usually very helpful. Thank you for all the great articles.

    Option #1 although good from the point of minimal logging and giving the option to add table locks if needed. It does not do one key thing the option #2 does,

    One of the prime reasons I use option #2 is to copy a table structure and data without knowing the source table structure.

    Is there a better option when the table structures are dynamic or unknown and have the advantages of option #1 of minimal logging?

    Thanks,
    Rahul

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s