I don’t usually do a lot with SQL, but lately I have found myself having to look at, and work with SQL a good bit. It seems that things come in waves.
Anyway, I was looking for a quick way to copy a table from one Database to another and came across this blog which I think has a lot of good queries just waiting to be tinkered with:
And the particular post that got me there was:
4:09 AM Posted by Suprotim Agarwal
Labels: SQL Server Administration
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).