March 12, 2008

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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

27 Responses to "Copy a table from one database to another in SQL Server 2005"
  1. Reginald said...
    August 5, 2008 at 12:48 PM

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

  2. averagecoder said...
    October 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

  3. Suprotim Agarwal said...
    October 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.

  4. Anonymous said...
    October 24, 2008 at 6:03 AM

    thanks for the help :-)

  5. Ganesan G said...
    October 29, 2008 at 12:09 AM

    Thanks a lot! Nice blog!

  6. Anonymous said...
    January 13, 2009 at 6:33 AM

    you're a star!

  7. Anonymous said...
    January 16, 2009 at 7:49 AM

    your a retard lmao

  8. Anonymous said...
    February 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.

  9. Anonymous said...
    July 20, 2009 at 7:26 AM

    Thanks for the Help!

  10. Anonymous said...
    August 6, 2009 at 11:42 PM

    Thanx it really saved my time and effort

  11. Anonymous said...
    October 7, 2009 at 2:22 AM

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

  12. Victor said...
    November 10, 2009 at 5:59 PM

    Thanks for the post.

  13. Anonymous said...
    December 4, 2009 at 11:40 PM

    thank u

  14. Anonymous said...
    February 25, 2010 at 5:26 AM

    thanks buddy

  15. Siva said...
    March 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.

  16. Anonymous said...
    April 21, 2010 at 12:43 PM

    Thank you for sharing knowledge and helping others with it.

    Amir

  17. Sankar said...
    May 25, 2010 at 10:08 AM

    From db2, type this in your query analyzer:

    INSERT INTO copytable SELECT * FROM db1.dbo.origtable

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

  18. Anonymous said...
    June 21, 2010 at 12:38 AM

    Thanks, it works!

  19. Anonymous said...
    October 13, 2010 at 2:51 PM

    Thank you!

    - venky

  20. Anonymous said...
    December 3, 2010 at 8:59 AM

    Thanks ! ! ! ! !

  21. Anonymous said...
    December 10, 2010 at 12:55 PM

    Thank you! Perfect

  22. Anonymous said...
    January 14, 2011 at 2:47 PM

    Thanks, dude. Still helpful.

  23. sasi said...
    February 12, 2011 at 12:40 AM

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

  24. High-Tech-Post said...
    November 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

  25. muhammad usman khan said...
    May 26, 2012 at 3:02 AM

    thanks alot dear......

  26. Anonymous said...
    May 30, 2012 at 2:13 AM

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

  27. Anonymous said...
    May 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

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions