How To Copy A Huge Table Data Into Another Table In Sql Server

In some cases, as a DBA, you are requested to re-create the schema and the content of specific tables from a database to another one in the aforementioned 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 exist used to perform table's data and schema re-create process. In order to go through each one of these methods, we will consider the beneath scenario:

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

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

Tables Copying Methods:

Using SELECT INTO Query

In guild to copy our tables from the AdventureWorks2018 database to the SQLShackDemo one, we tin 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 lot manage to copy the database objects such as the indexes and constraints, you need to generate script for it individually, afterwards that y'all need to apply the scripts to the destination database.

In our example, to re-create the Department , Employee , EmployeeDepartmentHistory and EmployeePayHistory tables under the HumanResources schema from the AdventureWorks2018 database to the SQLShackDemo database, nosotros volition run the below script:

Create schema HumanResources

Select * into SQLShackDemo . HumanResources . Department from

AdventureWorks2018 . HumanResources . Department

Select * into SQLShackDemo . HumanResources . Employee from

AdventureWorks2018 . HumanResources . Employee

Select * into SQLShackDemo . HumanResources . EmployeeDepartmentHistory from

AdventureWorks2018 . HumanResources . EmployeeDepartmentHistory

Select * into SQLShackDemo . HumanResources . EmployeePayHistory from

AdventureWorks2018 . HumanResources . EmployeePayHistory

The columns in destination tables are created in the lodge specified in the select statement. All these columns take the verbal proper noun, data type, nullability property, and column value as in the source table.

If whatsoever of the tables contains an Identity cavalcade, the new column in the destination table will inherit the Identity property without the need to plough on the Identity_Insert.

This is valid in near cases unless the Select argument contains Join, yous are using Matrimony 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 1 of these atmospheric condition mentioned is true, the column will be created with Not zippo property instead of inheriting the required Identity belongings.

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

Using SQL Server Export/Import sorcerer

Some other 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 consign from the source database or import from the destination one in guild 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 Information command:
In the Choose a Information 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 Consign Wizard, specify the destination Server name, the Hallmark method that will exist 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 Magician, choose the Copy data from one or more tables or views selection and click the Adjacent button:
In the Select Source Tables and Views step of the SQL Server Import and Export Sorcerer, choose the tables that will be copied from the chosen source database to the destination one:
In gild to make sure that the tables will exist created in the destination database, click on the Edit Mappings button and brand sure that the Create destination table pick is ticked, and if whatever of your tables comprise Identity column, brand sure to tick the Enable identity insert pick, then click the OK button. If you have more than one table to exist exported to the destination database, you lot need to go through the Edit Mappings check tabular array by table:
In the Select Source Tables and Views pace, click the Adjacent button, one time all tables are checked via the Edit Mappings

In the Salvage and Run Package step, click the Adjacent button:
In the Consummate the Wizard pace, click the Finish button:
Once the execution is completed successfully, y'all can view the steps followed to migrate the tables and the number of records transferred. Review the steps and the messages, if at that place is no error click the Shut push:

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 business 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 Strange keys connecting these tables together, you need to consign the data in the right 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 tin can be used to copy the tables' schema and data from the source database to the destination one in our example.

Using the SQL Server Management Studio, correct-click on the source database from the Object Explorer, then from Tasks choose the Generate Scripts command:
In the Choose Objects step, cull the Select specific database objects option to specify the tables that you will generate script for, and so choose the tables past ticking beside each one of it and click the Adjacent button:
In the Set Scripting Options step, specify the path where y'all will relieve the generated script file, and click the Advanced push button:
From the appeared Avant-garde Scripting Options window, specify Schema and data as Types of data to script:

You can decide from here if yous desire to script the indexes and keys in your tables.

Click the OK button and after getting back to the Avant-garde Scripting Options step, click the Next push

Review the Summary pace and click the Next button:
You can monitor the progress from the Save Scripts step. If in that location are no errors click the Finish push and you will find the script file in the specified path:

SQL Scripting method is useful to generate 1 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 lodge if there are relations between the tables.

Using the ApexSQL Diff and ApexSQL Data Unequal combination

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

ApexSQL Data Unequal also is another SQL tool that tin 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 Identity 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 information from the source tables to the destination ones by following these steps:

Start the ApexSQL Unequal tool
From the Data sources tab of the New project window, specify the source server name, the source database name and the Authentication method used to connect to the source server. You demand to specify a valid user name and password if you lot choose SQL Server authentication.

Besides specify the destination server proper noun, the destination database name and the Authentication method used to connect to the destination server. You need to specify a valid user name and countersign if yous choose SQL Server authentication.

Y'all can modify the function of each server specified as the source or destination i with a click on the Reverse button below information sources:
Once everything is set, click the Compare push
The main application window volition be shown, that contains the Results grid with objects that are common between the 2 databases and the objects exist in one of the databases only not existed in the second i:

In our case, we are interested in the objects that exist in the AdventureWorks2018 database and non be in the SQLShackDemo database, where we can find the tables to be copied.
Expand the objects that be in the AdventureWorks2018 database only, and choose the tables that you will copy:
Once the tables are checked, click the Synchronize push button from the Dwelling tab:
In the Synchronization management pace of the Synchronization wizard, click the Next push, if you lot don't want to modify the synchronization direction:
In the Dependencies step of the Synchronization wizard, uncheck the Include dependent database objects option, if yous don't desire to script the related tables and click the Next button:
In the Output options pace of the Synchronization wizard, choose the Create a synchronization script action if you desire to generate script and run it manually or the Synchronize now activeness to let the tool to run the script on the destination server directly and click the Adjacent button:
In the Summary and warning footstep of the Synchronization magician, review the Actions and click the Create script push button:

At present the script for creating the database tables with its objects are generated and in the correct club. You tin simply run this script on the destination database to create the tables with a click on the Execute push button:
Outset the ApexSQL Data Diff tool

From the Data sources tab of the New project window, 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 user name and password if you lot cull SQL Server hallmark.

Also specify the destination server proper name, the destination database proper name and the Hallmark method used to connect to the destination server. You lot need to specify a valid user name and password if you choose SQL Server authentication.

Y'all can alter the role of each server specified as the source or destination one with a click on the Opposite push:
Once everything is set, click the Compare button

The primary application window will exist shown, that contains the information differences betwixt the tables in the source and destination databases in addition to the identical and incomparable data.

Here nosotros are interested in the information differences between the tables in the source and destination databases, as in the previous steps we created these tables schema, only they are still empty.
From the differences result grid, choose the tables that you need to re-create its data to the destination database, then click the Synchronize button from the Home tab:
In the Synchronization direction step of the Synchronization wizard, click the Adjacent button, if you don't desire to change the synchronization management:
In the Output options step of the Synchronization wizard, choose the Create a synchronization script activeness, if you want to generate script and run information technology manually or the Synchronize at present action to allow the tool to run the script on the destination server and click the Side by side button:
In the Summary and warnings step of the Synchronization wizard, review the actions and click the Create script push button:

Now the script to insert the database tables, handling the Identity insert, are generated. Yous tin just run this script on the destination database with a click on the Execute button to insert the tables' information:

The combination of the ApexSQL Diff and ApexSQL Data Diff tools are useful to copy SQL database tables past generating consummate 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 tin 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 treatment the Identity column insert.

Commencement the ApexSQL Script tool
From the Select databases tab of the New projection window, specify the source server proper noun and the Authentication method used to connect to the source server and click the Connect button:

You lot need to specify a valid user name and password if you choose SQL Server authentication.
A list of all databases hosted in your server volition exist viewed in the correct database list. Choose the source database proper name which is AdventureWorks2018 in our example:
From the Options tab, under the Data section, select the Ready identity insert on option from the General sub-section and Insert from the Script row as sub-section and click the Load push:
One time the database is loaded, by default the Structure view is shown. Choose the tables that you will copy to the destination database and click the Information button from the Dwelling house tab under the View section:

Under the Data view, choose the same tables equally in the Structure view, then that information from those tables is included as well:
Once everything is set, click the Script push from the Home tab:
Cull Structure and data as the Scripting mode in the kickoff stride of the Script magician and click the Next button:
Cull SQL script as the Output type, from the Script magician and click the Side by side push:
In the Dependencies window, uncheck whatsoever dependent objects or get out all checked and click the Next push:
In the SQL script options step, choose the Open up script in editor pick then click the Create button:

Now a completed unmarried script is generated. Simply run information technology on the destination database and the tables will be copied completely. Yous can get through the script to check the tables' creation scripts in the correct club equally below:

Also, you lot tin can discover the Insert statements for the tables' data as follows:

ApexSQL Script is a very useful tool that can exist 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 lodge. Besides, no activity required from your side to handle the Identity insert.

Determination:

As you can see, multiple methods tin can be used to re-create 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 every bit indexes and keys. As well, these tools volition not handle the cosmos order for these tables if there are relations betwixt information technology. From the data side, most of these tools need extra steps from your side to handle the Identity insert. Using the combination of the ApexSQL Diff and ApexSQL Data Unequal or using ApexSQL Script, by a few numbers of elementary steps, you will have a complete script for the database tables' schema and data with no actress effort from your side to handle anything. Just run these scripts on the destination database and relish the result.

Writer
Contempo Posts

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and feel in SQL BI, SQL Server Database Administration and Development fields.

He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.

Also, he is contributing with his SQL tips in many blogs.

View all posts by Ahmad Yaseen