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

i think this will help you stackoverflow.com/questions/187770/… — Vinayak Pahalwan
– Vinayak Pahalwan, Commented Dec 28, 2012 at 6:42

Mahmoud Gamal · Accepted Answer · 2012-12-28 08:05:04Z

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

Royi Namir · Accepted Answer · 2015-07-01 18:35:07Z

Right click on the database, select tasks and click on Generate Scripts.
In the resultant pop-up, choose options as required (click advanced), to drop and create table, drop if exists, etc.
Scroll down and choose "Schema and Data" or "Data Only" or "Types of data to script (2008 R2)"as required.
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

Adeel Ahmed · Accepted Answer · 2012-12-28 06:36:45Z

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

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

Praveen Mitta · Accepted Answer · 2017-09-27 18:35:23Z

Below SQL Query will copy SQL Server table schema & data from one database to another database. You can always table name (SampleTable) in your destination database.

SELECT * INTO DestinationDB.dbo.SampleTable FROM SourceDB.dbo.SampleTable

Collectives™ on Stack Overflow

copy tables with data to another database in SQL Server 2008

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Related