Copy Data Tables From Another Database Using SQL Server Management Studio

2015-10-06


Sometimes we need to copy data tables from another database in SQL Server. There are multiple ways to do it. Here we show the steps using SQL Server Management Studio.

SQL Server Management Studio provides us a function named Import Data…, we can find this options by right click a database.

1: Right click the target database which you want to add data tables, select Tasks, then Import Data…

image

2: The Import and Export Wizard start, click Next button to continue:

image

3: Select the source database:

  • Set Data source to SQL Server Native Client 11.0;
  • Input Source Database Server name or IP address;
  • Input Source Database login information:
  • Remember click Refresh button to get right database which you want to copy from:
image

4: Select target database and input related information:

image

5: Choose "Copy data from one or more tables or views":

image

6: You will have to check on all data tables which you want to copy.

Note: If you have many tables which you need to copy, you might find you would be stuck here. It is hard to "quick" select multiple tables but you will have to try by yourself, might be the issue or problem from Management Studio itself, but we spent some time to successfully "quick select" multiple data tables here.

Also, please know there is a problem in our system data table: we have spaces in each data table! this will cause the data table import failed eventually. But here we just use a sample to show entire Import processing. please forget about the wrong data table names.

image

7: Previous steps just generated the package which going to run. Now it is the time to run:

image

8: The Wizard comes to the last step, just click Finish button to launch the copy process.

image

9: If the copy process successfully done, you will see the similar windows like below (Here used another import data sample screen because we mentioned already above since we had data table name error)

image

Now you get your data tables copied from other database.