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: Department, Employee, EmployeeDepartmentHistory 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
Select * into AdventureWorks_New.HumanResources.Department from
Select * into AdventureWorks_New.HumanResources.Employee from
Select * into AdventureWorks_New.HumanResources.EmployeeDepartmentHistory from AdventureWorks2018.HumanResources.EmployeeDepartmentHistory
Select * into AdventureWorks_New.HumanResources.EmployeePayHistory from
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:
- From SQL Server Management Studio, right-click on the AdventureWorks2018 database in the Object Explorer, then from Tasks choose the Export Data command:
- 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:
- 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:
- 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:
- 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:
- 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:
- In the Select Source Tables and Views step, click the Next button, once all tables are checked via the Edit Mappings.
- In the Save and Run Package step, click the Next button:
- In the Complete the Wizard step, click the Finish button:
- 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:
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.
- Using the SQL Server Management Studio, right-click on the source database from the Object Explorer, then from Tasks choose the Generate Scripts command:
- 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:
- In the Set Scripting Options step, specify the path where you will save the generated script file, and click the Advanced button:
- From the appeared Advanced Scripting Options window, specify Schema and data as Types of data to script:
- Click the OK button and after getting back to the Advanced Scripting Options step, click the Next button.
- Review the Summary step and click the Next button:
- 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:
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.