copy tables with data to another database in SQL Server 2008

Question

I have to copy the tables with data from one database to another using Query. I know how to copy tables with data within a database. But I was not sure about how to do the same for copying between two databases.

I have to copy huge number of tables, so I need any fast method using query...

Anybody please help out...Thanks in advance...

Answer 1

You can use the same way to copy the tables within one database, the SELECT INTO but use a fully qualified tables names database.schema.object_name instead like so:

USE TheOtherDB;

SELECT *
INTO NewTable
FROM TheFirstDB.Schemaname.OldTable

This will create a new table Newtable in the database TheOtherDB from the table OldTable whih belongs to the databaseTheFirstDB

Answer 2

1. Right click on the database, select tasks and click on Generate Scripts.
2. In the resultant pop-up, choose options as required (click advanced), to drop and create table, drop if exists, etc.
3. Scroll down and choose "Schema and Data" or "Data Only" or "Types of data to script (2008 R2)"as required.
4. Save to file and execute on the destination DB.

Advantages -

• Can be executed against the destination DB, even if it is on another server / instance
• Quickly script multiple tables, with data as needed

Warning - Might take quite a while to script, if the tables contain a large amount of data.

Rajan

Answer 3

INSERT INTO DB2.dbo.MyOtherTable (Col0, Col1)
SELECT Col0, Col1 FROM DB1.dbo.MyTable

Both table column's must have same data types..