Caveman's Blog

My commitment to learning.

Sql Server: How to copy a table?

with 27 comments


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.

Advertisements

27 Responses

Subscribe to comments with RSS.

  1. […] Sql Server: How to copy a table? « Caveman's Blog […]

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

  3. Excellent information, just saved my life.

    Nag

    May 11, 2010 at 1:17 pm

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

  5. Thank’s ^_^

    Nunung

    April 26, 2011 at 11:21 pm

  6. […] A good explanation from Caveman’s Blog here: Sql Server: How to copy a table? […]

  7. […] Quelle: Sql Server: How to copy a table? […]

  8. Thank’s ^_^

    Anonymous

    November 20, 2011 at 9:52 pm

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

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

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

  12. […] 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 […]

  13. Thanks for the help, I appreciate it!

    Amanda

    May 1, 2012 at 11:02 am

  14. Very much appreciated information. Very clear and concise

    Anonymous

    May 3, 2012 at 9:34 am

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

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

  17. thanks for this blog – really came in handy

    DS

    July 16, 2012 at 8:00 am

  18. For structure only you can use this:

    select top 0 * into from

    Arkadiy Trofman

    August 4, 2012 at 11:01 am

  19. thanks — useful article

    Anonymous

    August 16, 2012 at 12:04 pm

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


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

%d bloggers like this: