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 (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – UNION ALL and ORDER BY – How to Order Table Separately While Using UNION ALL
Next Post
SQL SERVER – Function to Round Up Time to Nearest Minute Interval

Related Posts

19 Comments. Leave new

  • 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.

    Reply
  • Look at SET IDENTITY_INSERT
    It allows you to insert identities.

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

    Reply
  • Hi,

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

    Thanks,
    Akash

    Reply
  • Hi Pinal ,
    with select into method I dont get the original table structure such as the key /primay .

    Reply
  • 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

    Reply
  • Fast and easy approach, Thanks Pinal..

    Reply
  • 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 :))))

    Reply
  • 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

    Reply
  • 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

    Reply
  • Great article

    Reply
  • 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”.

    Reply
  • 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

    Reply
  • sadinesh12@gmail.com
    August 21, 2013 5:46 pm

    very clear explanation, thank u so much boss,

    cheers

    Reply
  • how if the 2 tables are not in the same database ? thx in advance

    Reply
  • Thank u sir …thank you so much

    Reply
  • Vishal Dixit
    July 29, 2015 8:24 pm

    Hello, what to do to create table from another table with specific collumn in mssql

    Reply
    • Use tempdb
      go
      Create table SourceTable (i int, j char(10), k char(10))
      go
      insert into SourceTable values (1,’Pinal’,’Dave’)
      go
      Select i, j into DestinationTable from SourceTable
      go
      Select * from DestinationTable

      Reply
  • Arun Vishnoi
    July 21, 2017 9:26 am

    Hello Sir,

    Can u tell me how to copy 1 core data into the same structure table 1 table 5 core data and another table blank how to move 1 core data in to anoter table fast.

    Reply

Leave a Reply

Menu