How to copy a table from one database to another database

14 January,2016 by Jack Vamvas

Question: I want to copy some tables from one database to another database in the same SQL Server instance. The copy should include creating a new table with the same ddl, indexes and constraints,triggers . Once the table is created the data  requires to be moved

Answer: As with a lot of things in SQL Server, it is possible to complete this task in multiple ways. This gives you the flexibility  for your particular requirements. Before you start I’d make sure the database is backed up and you have recovery point in case of loss. I've listed 3 methods , and added some other methods through links at the bottom of this post

Method 1 : Export/import wizard

If you only want to copy tables and not concerned with relationships, this is a quick and manageable method.

Right Click on the database | Tasks | Export Data   (or Import Data) 

Import_export_wizard

Some things you may need to consider when using the Import\Export Wizard method

  1. Enable Identity Insert –Enable “Enable Identity Insert” if you need values to be inserted into a identity field. Therefore the exact identity values move from source database to destination table.The “Enable Identity Insert” checkbox is configurable per object .
  2. If you don’t want to lose your indexes and keys , you’ll need to generate\create a script to create keys, indexes and table if necessary. You can then progress with the data import 
  3. Note:If FOREIGN KEYS exist ,you’ll need to import in the right order

Method 2 Generate scripts wizard

Generate scripts for all tables and other objects  you want to migrate. Right click on the database | Tasks | Generate scripts | select tables and objects you want to migrate.

This method requires some review before you execute the scripts. By default, SSMS doesn’t order the scripts in the correct order

Once you’ve created the Destination object , you can use the Import\Export Wizard.

Method 3 Third party tools

Over time I’ve used a few different tools. I’ve had good experiences with a few different tools – ApexSQLDiff and SQL Compare – are two examples of comprehensive tools.These tools require licenses, but the Trial period gives you quite a bit of functionality.

Read More on export and import of sql data

 bcp - export and import data on SQL Server - SQL Server DBA

SQL Server - Bulk Insert CSV into a SQL Server table - SQL

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to copy a table from one database to another database


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer