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