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:
Copy a table from one database to another in SQL Server 2005
4:09 AM Posted by Suprotim Agarwal
Labels: SQL Server AdministrationIf 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).