Ahmad Yaseen

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

April 29, 2016 by

In some cases, as a DBA, you are requested to copy the schema and the content of specific tables from a database to another one in the same instance or in a different SQL instance, such as copying specific tables from a production database to a DEV one for testing or troubleshooting purposes.

SQL Server offers a lot of methods that can be used to perform table’s data and schema copy process. In order to go through each one of these methods, we will consider the below scenario:

The hosting SQL Server: localhost. Both databases hosted in the same SQL Server 2014 instance.
The source database: AdventureWorks2012.
The destination database: SQLShackDemo.

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

Tables Copying Methods:

Using SELECT INTO Query

In order to copy our tables from the AdventureWorks2012 database to the SQLShackDemo 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.

In our example, to copy the Department, Employee , EmployeeDepartmentHistory and EmployeePayHistory tables under the HumanResources schema from the AdventureWorks2012 database to the SQLShackDemo database, we will run the below script:

The columns in destination tables are created in the order specified in the select statement. All these columns have the exact name, data type, nullability property, and column value as in the source table.

If any of the tables contains an IDENTITY column, the new column in the destination table will inherit the IDENTITY property without the need to turn on the IDENTITY_INSERT.

This is valid in most cases unless the SELECT statement contains JOIN, you are using UNION to join multiple SELECT statements, the IDENTITY column is mentioned many times in your select statement or the source of this IDENTITY column is a remote data source.

If any one of these conditions mentioned is true, the column will be created with NOT NULL property instead of inheriting the required IDENTITY property. 

To overcome this IDENTITY issue, you can use the IDENTITY SQL function in the select statement to create the IDENTITY column.

Using SQL Server Export / Import wizard

Another method that can be used 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 AdventureWorks2012 database in the object explorer, then choose Tasks -> Export Data:

  2. In the Choose a Data Source window of the 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 Next.

  3. In the Choose a Destination window of the SQL 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 Next.

  4. In the Specify Table Copy or Query of the SQL Import and Export Wizard, choose Copy data from one or more tables or views. Click Next.

  5. In the Select Source Tables and Views of the SQL Import and Export Wizard, choose the tables that will be copied from the chosen source database to the destination one, then click Next.

  6. In order to make sure that the tables will be created in the destination database, click on Edit Mappings and make sure that the Create destination table is ticked, and if any of your tables contain IDENTITY column, make sure to tick Enable Identity Insert, then click OK. If you have more than one table to be exported to the destination database, you need to go through the Edit Mapping check table by table.

  7. In the Select Source Tables and Views click Next.

  8. In the Run Package, click Next.

  9. In the complete the wizard, click Finish.

  10. >
  11. 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 Close.

This method is a quick way to copy tables from the source database to the destination one if you arrange to copy the tables with no concern with the tables’ relationships and orders.

Using this method, the tables’ Indexes and keys will not be transferred. If you are interested in copying it, you need to generate scripts for these database objects.

If these are Foreign Keys connecting these tables together, you need to export the data in the correct order, otherwise the export wizard will fail.

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 Generate Scripts.

  2. In the Choose objects window, choose Select Specific Database Objects to specify the tables that you will generate script for, then choose the tables by ticking beside each one of it. Click Next.

  3. In the Set Scripting Options window, specify the path where you will save the generated script file, and click Advanced.

  4. From the appeared Advanced Scripting Options window, specify Schema and Data as Types of Data to Script. You can decide from here if you want to script the indexes and keys in your tables. Click OK.

  5. Getting back to the Advanced Scripting Options window, click Next.

  6. Review the Summary window and click Next.

  7. You can monitor the progress from the Save or Publish Scripts window. If there is no error click Finish 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.

Using the ApexSQL Diff and ApexSQL Data Diff combination

ApexSQL Diff is a useful SQL tool that can be used to find the differences between the databases from schema side and generate synchronization script to create these tables in the destination database in the correct order.

ApexSQL Data Diff also is another SQL tool that can be used to find the differences between the databases from data side and generate synchronization script to insert the data in the destination database tables, taking into consideration the IDENITY columns.

We can take benefits of these two tools together to generate the required scripts to create the tables to be copied with its objects, and copy the data from the source tables to the destination ones.

  1. Start the ApexSQL Diff tool.

  2. From the Data sources panel, specify the source server name, the source database name and the Authentication method used to connect to the source server. You need to specify a valid use name and password if you choose SQL Server Authentication.

    Also specify the destination server name, the destination database name and the Authentication method used to connect to the destination server. You need to specify a valid use name and password if you choose SQL Server Authentication.

    You can change the role of each server specified as the source or destination one by clicking on Reverse. Then click Connect.

  3. Click Compare.

    A new window will be shown, that contains the objects that are common between the two databases and the objects exist in one of the databases but not existed in the second one.

    In our example, we are interested in the objects that exist in the AdventureWorks2012 database and not exist in the SQLShackDemo database, where we can find the tables to be copied.

  4. Expand the objects that exist in the AdventureWorks2012 database only, and choose the tables that you will copy. Click Synchronize.

  5. In the Synchronization Direction of the Synchronization wizard window, click Next if you don’t want to change the Synchronization direction.

  6. In the Dependencies of the Synchronization wizard window, uncheck the Include dependent database objects if you don’t want to script the related tables. Click Next.

  7. In the Output options of the Synchronization wizard window, choose to Create a synchronization script if you want to generate script and run it manually or Synchronize now to let the tool to run the script on the destination server directly. Click Next.

  8. In the Summary and warning of the Synchronization wizard window, review the Actions and click Create Script.

    Now the script for creating the database tables with its objects are generated and in the correct order. You can simply run this script on the destination database to create the tables.

  9. Start the ApexSQL Data Diff tool.

  10. From the Data sources panel, specify the source server name, the source database name and the Authentication method used to connect to the source server. You need to specify a valid use name and password if you choose SQL Server Authentication.

    Also specify the destination server name, the destination database name and the Authentication method used to connect to the destination server. You need to specify a valid use name and password if you choose SQL Server Authentication.

    You can change the role of each server specified as the source or destination one by clicking on Reverse. Then click Connect.

  11. Click Compare.

    A new window will be shown, that contains the data differences between the tables in the source and destination databases in addition to the identical and incomparable data.

    Here we are interested in the differences between the tables in the source and destination databases. As in the previous steps we created these tables but it still empty.

  12. From the differences result grid, choose the tables that you need to copy its data to the destination database, then click Synchronize.

  13. In the Synchronization Direction of the Synchronization wizard window, click Next if you don’t want to change the Synchronization direction.

  14. In the Output options of the Synchronization wizard window, choose to Create a synchronization script if you want to generate script and run it manually or Synchronize now to let the tool to run the script on the destination server. Click Next.

  15. In the Summary and Warnings of the Synchronization wizard window, review the actions and click Create Script.

Now the script to insert the database tables, handling the IDENTITY insert, are generated. You can simply run this script on the destination database to insert the tables’ data.

The combination of the ApexSQL Diff and ApexSQL Data Diff tools are useful to copy SQL database tables by generating a completed scripts for these tables’ schema with indexes and keys and in the correct order, and the tables’ data handling the IDENTITY column insert.

Using ApexSQL Script:

Another useful tool provided by ApexSQL that can be used to copy SQL Server tables data and schema from the source database to the destination one is the ApexSQL Script tool. This nice tool will create script for the database tables schema and data with the indexes and keys of these tables handling the IDENTITY column insert.

  1. Start the ApexSQL Script tool.

  2. From the Select Databases panel, specify the source server name and the Authentication method used to connect to the source server. You need to specify a valid use name and password if you choose SQL Server Authentication. Click Connect.

  3. A list of all databases hosted in your server will be viewed in the right result grid. Choose the source database name which is AdventureWorks2012 in our example.

  4. From the Data panel, select the Set IDENTITY_INSERT ON from the General section and INSERT from the Script row as section. Click Open.

  5. From the shown window, choose the tables that you will copy to the destination database and click Script:

  6. Choose Structure and Data as Scripting mode and T-SQL as the Output type from the Script Wizard. Click Next.

  7. In the Dependencies window, click Next.

  8. In the Output file window, from the Action drop down list, choose create and write to file. Then you have the choice to save the scrip to a specific path or open it in an editor. Choose Open script in editor then click Create.

Now a completed single script is generated. Just run it on the destination database and the tables will be copied completely. You can go through the script to check the tables’ creation scripts in the correct order as below:

Also you can find the INSERT statements for the tables’ data as follows:

ApexSQL Script is a very useful tool that can be used to copy SQL Server database tables from the source database to the destination one without any effort from your side to handle the tables’ creation order. Also no action required from your side to handle the IDENTITY insert.

Conclusion:

As you can see, multiple methods can be used to copy tables from the source database to the destination one, including schema and data. Most of these tools require a big effort from your side to copy the tables’ objects such as indexes and keys. Also these tools will not handle the creation order for these tables if there are relations between it. From the data side, most of these tools need an extra steps from your side to handle the IDENTITY insert. Using the combination of the ApexSQL Diff and ApexSQL Data Diff or using ApexSQL Script, by a few number of simple steps, you will have a complete script for the database tables’ schema and data with no extra effort from your side to handle anything. Just run these scripts on the destination database and enjoy the result.

Useful links:


Ahmad Yaseen

Ahmad Yaseen

Ahmad Yaseen is Senior SQL Server DBA at Aramex International Company with a bachelor’s degree in computer engineering as well as .NET development experience.

He is a Microsoft Certified Professional with a good experience in SQL server development, administration, performance tuning, monitoring and high availability and disaster recovery technologies. Also he is contributing with his SQL tips in many blogs

View all posts by Ahmad Yaseen
Ahmad Yaseen
SQL Server maintenance

About Ahmad Yaseen

Ahmad Yaseen is Senior SQL Server DBA at Aramex International Company with a bachelor’s degree in computer engineering as well as .NET development experience. He is a Microsoft Certified Professional with a good experience in SQL server development, administration, performance tuning, monitoring and high availability and disaster recovery technologies. Also he is contributing with his SQL tips in many blogs View all posts by Ahmad Yaseen

39,989 Views