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


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

27 comments:

Reginald said...

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

averagecoder said...

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

Suprotim Agarwal said...

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.

Anonymous said...

thanks for the help :-)

Ganesan G said...

Thanks a lot! Nice blog!

Anonymous said...

you're a star!

Anonymous said...

your a retard lmao

Anonymous said...

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.

Anonymous said...

Thanks for the Help!

Anonymous said...

Thanx it really saved my time and effort

Anonymous said...

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

Victor said...

Thanks for the post.

Anonymous said...

thank u

Anonymous said...

thanks buddy

Siva said...

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

Nice blog.

Anonymous said...

Thank you for sharing knowledge and helping others with it.

Amir

Sankar said...

From db2, type this in your query analyzer:

INSERT INTO copytable SELECT * FROM db1.dbo.origtable

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

Anonymous said...

Thanks, it works!

Anonymous said...

Thank you!

- venky

Anonymous said...

Thanks ! ! ! ! !

Anonymous said...

Thank you! Perfect

Anonymous said...

Thanks, dude. Still helpful.

sasi said...

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

High-Tech-Post said...

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

muhammad usman khan said...

thanks alot dear......

Anonymous said...

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

Anonymous said...

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


my email ID is : aju184@gmail.com