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, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

27 comments:

planetregin said...

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

Anonymous 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 :-)

Anonymous 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

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

Unknown said...

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

Rahul 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

Unknown 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