Copy a table from one database to another in SQL Server 2005

If you have a table in a database and you would like to copy the table to another database, use this query:

SELECT * INTO AdventureWorks.dbo.CustomersTemp FROM Northwind.dbo.Customers

Just remember that using this query will only transfer the schema and data. It does not transfer the indexes, foreign keys, statistics etc.

If you want to transfer all the objects from one database to another, open Sql Server Management Studio > Right click on your database > All Tasks > Generate SQL Scripts. Then run these scripts against the new database.

Transfer both schema and data

To copy both data and schema, use the Microsoft SQL Server Database Publishing Wizard 1.1. This tool works for both SQL 2000 and SQL 2005 and generates a single SQL script file which can be used to recreate a database (both schema and data).
at

27 comments:

  1. planetreginAugust 5, 2008 at 12:48 PM

    Thank you! I was searching the net to copy a table and found ur blog.

    ReplyDelete
  2. AnonymousOctober 21, 2008 at 2:26 AM

    I am still looking for pure copy table without using SQL Statement (SQLServer SMO is prefered) but I think I am going to use your tips if none I can find...

    Nice blog :)
    ___________

    Daniel - averagecoder.net

    ReplyDelete
  3. Suprotim AgarwalOctober 22, 2008 at 9:45 AM

    averagecoder: The solution you choose also depends on the size of your table. Using the scripts 'may' not feasible if your table contains a million rows.

    ReplyDelete
  4. AnonymousOctober 24, 2008 at 6:03 AM

    thanks for the help :-)

    ReplyDelete
  5. AnonymousOctober 29, 2008 at 12:09 AM

    Thanks a lot! Nice blog!

    ReplyDelete
  6. AnonymousJanuary 13, 2009 at 6:33 AM

    you're a star!

    ReplyDelete
  7. AnonymousJanuary 16, 2009 at 7:49 AM

    your a retard lmao

    ReplyDelete
  8. AnonymousFebruary 4, 2009 at 3:15 AM

    Thanks for this. But does this work with large tables?
    Averagecoder - have you tried Import/Export as a solution to copying tables? Unfortunately, this does not work with tables with 1000s of rows.

    ReplyDelete
  9. AnonymousJuly 20, 2009 at 7:26 AM

    Thanks for the Help!

    ReplyDelete
  10. AnonymousAugust 6, 2009 at 11:42 PM

    Thanx it really saved my time and effort

    ReplyDelete
  11. AnonymousOctober 7, 2009 at 2:22 AM

    it creates a new table. i want to copy just data from one table to another

    ReplyDelete
  12. vnasibiNovember 10, 2009 at 5:59 PM

    Thanks for the post.

    ReplyDelete
  13. AnonymousDecember 4, 2009 at 11:40 PM

    thank u

    ReplyDelete
  14. AnonymousFebruary 25, 2010 at 5:26 AM

    thanks buddy

    ReplyDelete
  15. SivaMarch 27, 2010 at 9:35 AM

    Thankyou so much buddy, I tried in many ways to get this done, but finally your solution helped me.

    Nice blog.

    ReplyDelete
  16. AnonymousApril 21, 2010 at 12:43 PM

    Thank you for sharing knowledge and helping others with it.

    Amir

    ReplyDelete
  17. SankarMay 25, 2010 at 10:08 AM

    From db2, type this in your query analyzer:

    INSERT INTO copytable SELECT * FROM db1.dbo.origtable

    ...........................Thankyou

    ReplyDelete
  18. AnonymousJune 21, 2010 at 12:38 AM

    Thanks, it works!

    ReplyDelete
  19. AnonymousOctober 13, 2010 at 2:51 PM

    Thank you!

    - venky

    ReplyDelete
  20. AnonymousDecember 3, 2010 at 8:59 AM

    Thanks ! ! ! ! !

    ReplyDelete
  21. AnonymousDecember 10, 2010 at 12:55 PM

    Thank you! Perfect

    ReplyDelete
  22. AnonymousJanuary 14, 2011 at 2:47 PM

    Thanks, dude. Still helpful.

    ReplyDelete
  23. UnknownFebruary 12, 2011 at 12:40 AM

    insert into Suprotim_Agarwal_comments values('Thanks a lot bro');

    ReplyDelete
  24. RahulNovember 3, 2011 at 11:31 PM

    Hi,

    http://hightechpost.blogspot.com/2011/10/copying-table-in-sql-server-2005.html

    check it in simple way for:

    1)Copy table from same Database:

    2)Copy table from another Database:

    3)Copy few columns into the destination table:

    regards
    hightechpost

    ReplyDelete
  25. UnknownMay 26, 2012 at 3:02 AM

    thanks alot dear......

    ReplyDelete
  26. AnonymousMay 30, 2012 at 2:13 AM

    thanks but my databases are present on different machine (server) then what should i do?

    ReplyDelete
  27. AnonymousMay 30, 2012 at 2:14 AM

    thanks but my databases are present on different machine (server) then what should i do?


    my email ID is : aju184@gmail.com

    ReplyDelete