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