Copy tables from one database to another in SQL Server

Option 1 :

SQL Server Management Studio's "Import Data" task (right-click on the DB name, then tasks) will do most of this for you. Run it from the database you want to copy the data into.

If the tables don't exist it will create them for you, but you'll probably have to recreate any indexes and such. If the tables do exist, it will append the new data by default but you can adjust that (edit mappings) so it will delete all existing data.

I use this all the time and it works fairly well.

Option 2 :

This should work:

SELECT * INTO DestinationDB..MyDestinationTable FROM SourceDB..MySourceTable 

It will not copy constaints, defaults or indexes. The table created will not have a clustered index.

Alternatively you could:

INSERT INTO DestinationDB..MyDestinationTable SELECT * FROM SourceDB..MySourceTable

If your destination table exists and is empty.

Option 3 :

1. Script the create table in management studio, run that script in bar to create the table. (Right click table in object explorer, script table as, create to...)
2. INSERT bar.[schema].table SELECT * FROM foo.[schema].table
   
   
   Source : StackOverFlow
   
   

Share this