How to copy tables from one database to another in SQL Server

SQL Server offers a lot of methods that can be used to perform table’s data and schema copy process.

The hosting SQL Server: localhost
The source database: AdventureWorks2018
The destination database: AdventureWorks_New
Both databases hosted: SQL Server 2017

The tables that will be copied from the source database to the destination one are: DepartmentEmployeeEmployeeDepartmentHistory and EmployeePayHistory under the HumanResources schema.

Following are the Tables Copying Methods:

Using SELECT INTO Query

In order to copy our tables from the AdventureWorks2018 database to the AdventureWorks_New one, we can use the Select Into SQL statement. This statement will create the tables in the destination database first, then it will copy the data to these tables. If you manage to copy the database objects such as the indexes and constraints, you need to generate script for it individually, after that you need to apply the scripts to the destination database.

Create schema HumanResources
Go
Select * into AdventureWorks_New.HumanResources.Department from
AdventureWorks2018.HumanResources.Department
Select * into AdventureWorks_New.HumanResources.Employee from
AdventureWorks2018.HumanResources.Employee
Select * into AdventureWorks_New.HumanResources.EmployeeDepartmentHistory from AdventureWorks2018.HumanResources.EmployeeDepartmentHistory
Select * into AdventureWorks_New.HumanResources.EmployeePayHistory from
AdventureWorks2018.HumanResources.EmployeePayHistory

Using SQL Server Export/Import wizard

To copy tables from the source database to the destination one is the SQL Server Export and Import wizard, which is available in SQL Server Management Studio. You have the choice to export from the source database or import from the destination one in order to transfer the data:

  1. From SQL Server Management Studio, right-click on the AdventureWorks2018 database in the Object Explorer, then from Tasks choose the Export Data command:
SQL Server Export/Import wizard
  1. In the Choose a Data Source step of the SQL Server Import and Export Wizard, specify the source Server name, the Authentication method that will be used to connect to the source server, and the source Database name, then click the Next button:
SQL Server Import and Export Wizard
  1. In the Choose a Destination step of the SQL Server Import and Export Wizard, specify the destination Server name, the Authentication method that will be used to connect to the destination server and the destination Database name, then click the Next button:
Choose a Destination
  1. In the Specify Table Copy or Query step of the SQL Server Import and Export Wizard, choose the Copy data from one or more tables or views option and click the Next button:
Specify Table Copy or Query
  1. In the Select Source Tables and Views step of the SQL Server Import and Export Wizard, choose the tables that will be copied from the chosen source database to the destination one:
Select Source Tables and Views
  1. In order to make sure that the tables will be created in the destination database, click on the Edit Mappings button and make sure that the Create destination table option is ticked, and if any of your tables contain Identity column, make sure to tick the Enable identity insert option, then click the OK button. If you have more than one table to be exported to the destination database, you need to go through the Edit Mappings check table by table:
Edit Mappings
  1. In the Select Source Tables and Views step, click the Next button, once all tables are checked via the Edit Mappings.
  1. In the Save and Run Package step, click the Next button:
Save and Run Package
  1. In the Complete the Wizard step, click the Finish button:
Complete the Wizard
  1. Once the execution is completed successfully, you can view the steps followed to migrate the tables and the number of records transferred. Review the steps and the messages, if there is no error click the Close button:
execution is completed successfully

Using Generate Scripts

SQL Server provides another way to generate script for the SQL Server databases with its objects and data. This script can be used to copy the tables’ schema and data from the source database to the destination one in our case.

  1. Using the SQL Server Management Studio, right-click on the source database from the Object Explorer, then from Tasks choose the Generate Scripts command:
Object Explorer
  1. In the Choose Objects step, choose the Select specific database objects option to specify the tables that you will generate script for, then choose the tables by ticking beside each one of it and click the Next button:
Select specific database objects
  1. In the Set Scripting Options step, specify the path where you will save the generated script file, and click the Advanced button:
Set Scripting Options
  1. From the appeared Advanced Scripting Options window, specify Schema and data as Types of data to script:
Schema and data
  1. Click the OK button and after getting back to the Advanced Scripting Options step, click the Next button.
  1. Review the Summary step and click the Next button:
Summary
  1. You can monitor the progress from the Save Scripts step. If there are no errors click the Finish button and you will find the script file in the specified path:
Save Scripts

SQL Scripting method is useful to generate one single script for the tables’ schema and data, including the indexes and keys. But again, this method doesn’t generate the tables’ creation script in the correct order if there are relations between the tables.

Published by Abdul Samad

Having 17+ years of extensive experience in IT industry, enabled to enhance the team performance and maximize customer satisfaction by strategically managing calls and implementing process improvements. Demonstrated ability to solve problems, meets challenging goals, and expedites delivery. Skilled MSSQL administrator guide team during the crisis situation. Apply Creative thoughts process in re-designing the workflow system to eliminate duplication of effort and increase productivity.

Leave a Reply