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.
[youtube=http://www.youtube.com/watch?v=FVWIA-ACMNo]
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 (https://blog.sqlauthority.com)
20 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.
Look at SET IDENTITY_INSERT
It allows you to insert identities.
I would like to meet those developers personaly who were using cursor to copy data from one table to another table.
Hi,
I would like to meet those developers personaly who were using cursor to copy data from one table to another table.
Thanks,
Akash
Hi Pinal ,
with select into method I dont get the original table structure such as the key /primay .
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
Fast and easy approach, Thanks Pinal..
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 :))))
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
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
Great article
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”.
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
very clear explanation, thank u so much boss,
cheers
how if the 2 tables are not in the same database ? thx in advance
Thank u sir …thank you so much
Hello, what to do to create table from another table with specific collumn in mssql
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
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.
you can use partition switching
ALTER TABLE Table1 SWITCH PARTITION x TO Table2