Tuesday, October 2, 2012

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

0 Comment to "Copy tables from one database to another in SQL Server"

Post a Comment