Sql Server: How to copy a table?
This is the simplest way to copy a table into another (new) table in the same SQL Server database. This way of copying does not copy constraints and indexes.
select * into <destination table> from <source table> Example: Select * into employee_backup from employee
We can also select only a few columns into the destination table like below
select col1, col2, col3 into <destination table> from <source table> Example: Select empId, empFirstName, empLastName, emgAge into employee_backup from employee
Use this to copy only the structure of the source table.
select * into <destination table> from <source table> where 1 = 2 Example: select * into employee_backup from employee where 1=2
Use this to copy a table across two database in the same Sql Server.
select * into <destination database.dbo.destination table> from <source database.dbo.source table> Example: select * into Mydatabase2.dbo.employee_backup from mydatabase1.dbo.employee
Any one of the following methods can be employed to copy a table into a destination database on a different SQL Server.
1. Data Transformation Service (DTS) – SQL Server 2000.
2. SQL Server Integration Service (SSIS) – SQL Server 2005
3. SQL Server “Export Data” task. – SQL Server 2000/2005
4. Create a linked Server of the destination SQL Server on the source SQL Server and then copy the table. – SQL Server 2000/ 2005.
5. We can also use sp_generate_inserts to generate data insertion scripts and then run the insert scripts.
6. I almost forgot this 😉 you can open the source table , select the row(s), copy (ctrl + C) the row(s), open the destination table and then paste (ctrl + V) the row(s).
Update: 3/27/2013: Check out my other post on how to copy a large table.
[…] Sql Server: How to copy a table? « Caveman's Blog […]
» OSCOMMERCE USER DATA SHARING by caffreys79 telephone system
June 10, 2009 at 5:47 am
Hey blog owner, want a free consultation on how to get more traffic? We are live consulting now.
Best-SEO
March 6, 2010 at 11:13 pm
Excellent information, just saved my life.
Nag
May 11, 2010 at 1:17 pm
I have a little problem.. how can I transfer a data from one DB (tableX) to other DB (same structure tableX) if the table already exists and have some data… I want to append it…
It gives me error that that table already exists… any solution? Thanks.
Spider
December 11, 2010 at 11:06 am
Since your table already exits in the destination database, you will have to write something like below:
insert into
select * from where [condition]
cavemansblog
December 19, 2010 at 2:17 am
Thank you for the clear example; it was exactly what I was looking for!
Dennis
November 25, 2011 at 10:55 am
Thank’s ^_^
Nunung
April 26, 2011 at 11:21 pm
U r welcome
cavemansblog
April 27, 2011 at 5:52 pm
[…] A good explanation from Caveman’s Blog here: Sql Server: How to copy a table? […]
TSQL Tip O’ the Week – 6/21/11 – Copy a Table Structure « SQL Feather and Quill
June 21, 2011 at 10:28 pm
[…] Quelle: Sql Server: How to copy a table? […]
Tabelle mit Struktur und Inhalt kopieren - Ralf Hohoff
October 18, 2011 at 9:31 am
Thank’s ^_^
Anonymous
November 20, 2011 at 9:52 pm
Can you use these methods with a “Where” clause? E.g., suppose I only want some sub-set of the records to be copied and pasted into the new table? Thanks!
Jenny
January 7, 2012 at 4:44 pm
You certainly can.
cavemansblog
January 12, 2012 at 10:07 am
pl. help me , I am new to Programming.
On a button click event of my asp.net application , I want to create a table which has name of inputed text of TextBox.
So , Pl. Tell me..
Thanks a lot in advance.
– Maunank Shah
REply on shah11592@gmail.com
maunank Shah
February 8, 2012 at 11:14 am
go to my pc login…
[…]Sql Server: How to copy a table? « Caveman's Blog[…]…
go to my pc login
March 2, 2012 at 6:30 pm
[…] by cavemansblog on March 27, 2012 This blog is an extension to my earlier post on how to copy a table. This post is specific to the situation where you have huge amount of data in your source table […]
SQL Server: How to copy a very large table? « Caveman's Blog
March 27, 2012 at 1:58 am
Thanks for the help, I appreciate it!
Amanda
May 1, 2012 at 11:02 am
Very much appreciated information. Very clear and concise
Anonymous
May 3, 2012 at 9:34 am
Thanks caveman. Didn’t know cavemen knew SQL, but then again they did find early versions of SQL on cave walls in France, along with bone fragments, pottery shards, and projectile points.
Anonymous
May 7, 2012 at 9:56 am
but what if I want to copy only the skeleton, i.e column names, pk’s, identities?
PotatoJam
May 30, 2012 at 3:18 am
Generate a Table script for the table using SSMS. Rename the table name in the script with the intended table name and execute the script to create a clone without any data.
cavemansblog
May 30, 2012 at 11:49 pm
thanks, but that would not have worked in my case. I has looking for a t-sql statement that will work on any table. Found a way around it.
PotatoJam
May 31, 2012 at 1:51 am
Would you mind sharing your approach so that others (including myself) could gain from your experience?
cavemansblog
June 1, 2012 at 12:32 am
thanks for this blog – really came in handy
DS
July 16, 2012 at 8:00 am
For structure only you can use this:
select top 0 * into from
Arkadiy Trofman
August 4, 2012 at 11:01 am
thanks — useful article
Anonymous
August 16, 2012 at 12:04 pm
Really nice info. I teach database at the university level and my students must implement project DB in a dispersed group environment with no shared server. This approach can be helpful as it is so quick, but do you know of a syntax for copying structures that will also carry along the table and attribute descriptions? I’m an absolute tyrant on implementing these. Currently the group members will work on tables independently, script each, and bring the scripts together in one location where they will build the consolidated DB.
Larry
June 4, 2013 at 8:17 pm