SQL Server bulk copy and bulk import and export techniques

Sometimes, it is necessary to export or import large amounts of data into or out of a database. Those operations are called bulk export and import operations.

SQL Server provides tools to perform bulk import and export and bulk copy operations:

The bcp utility
The Openrowset(Bulk) function
The SQL Server import and export wizard
The Bulk insert statement
The Select into statement

This article explains how to copy large amounts of data from one table or set of tables to other databases using these tools and using ApexSQL Script and ApexSQL Data Diff.

Copying data by using the SQL Server import and export wizard

The Import and export wizard creates an Integration Services (SSIS) package that can be scheduled to run on a specified basis and modified by using SQL Server Data Tools.

To start the Import and export wizard, right-click the database and select the Tasks sub-menu -> Export data command:

In the Choose a data source step, connect to a source database. The following permissions are required to use the SQL Server import and export wizard on a source destination: read data from the database or file, Insert permission on the msdb database to save the SSIS package:
In the Choose a destination step, connect to a destination database. Permissions required for a destination instance are: write data to the database or file, permissions to create a database or a table if necessary:
In the Specify table copy or query step, choose the Copy data from one or more tables or views option:
Choose a table for which you want to export data in the Select source tables and views step:
Choose the Run immediately option in the Save and run package step:
Click the Finish button in the Complete the wizard step:
Once the execution is finished, the following window will be shown:

Note that the SQL Server import and export wizard will not treat an identity column differently than any other column and it will fail when trying to insert data into a table that has an identity column:

If a source table has a foreign key constraint note that the SQL Server import and export wizard does not load tables a specific order for constraints, and it is possible that a table containing a foreign key will get loaded before a table containing a referenced primary key, which will cause a foreign key constraint failure.

SQL Server bulk copy techniques

bcp utility

The bcp utility is a command-line tool that enables bulk export of data from a source database to a file or bulk data import from a file into a destination database. For example, to export all EmployeeIDs and Emails to the Emails.txt file from the EmployeeEmail table in the TestDB database, run the following bcp command:

bcp TestDB.dbo.EmployeeEmail out Emails.txt –c -T

The -c option specifies that the utility is being used with character data and that the -T option states a trusted connection, meaning that the bcp utility uses the login credentials of the user that is currently logged. If the -T option is not specified a username and password must be specified with the -U and -P options.

The table must exist prior to the import. Before the data is imported into a destination table, the table in a destination instance has to have columns that match the data type to be imported. To insert data from the Emails.txt file into the NewSchema.dbo.EmployeeEmail table use the following bcp command:

bcp NewSchema.dbo.EmployeeEmail in Emails.txt -T –c

The bcp utility can only export data to a file or import data from a file. The security principal must have Select permission on the source table to use the utility.

Import data by using the Bulk insert statement

To import data from a specified file to a table, the Bulk insert statement can be used. Same as the bcp utility, the destination table has to exist and to have column types that match the datatypes that will be imported.

Specify the destination table and the location of the file from which to import the data:

BULK INSERT TestRemote.dbo.EmployeeEmail 
FROM 'C:\Users\Lenovo\Desktop\Emails.txt';
GO

Import data by using the Openrowset(Bulk) function

The Openrowset(Bulk) function connects to an OLE DB data source to restore data and it allows access to a remote data by connecting to a remote data source.

INSERT INTO AllEmails(Email)
SELECT * FROM OPENROWSET(
   BULK 'C:\Users\Lenovo\Desktop\Emails.txt',
   SINGLE_BLOB) AS x;

The Openrowset(Bulk) function provides an alternative to accessing objects from a linked server and it is suitable for one-off entry of data from a remote source.

Import and export data by using Select into statement

When the Into clause is used in a combination with the Select statement enables creating a new table based on the result set of the Select statement. For example, to copy the EmployeeEmail table, on the same instance, in the default schema of the TestDB database, run the following query:

SELECT * INTO TestDB.dbo.EmployeeEmail 
FROM AdventureWorks2012.HumanResources.EmployeeEmail;

Select into cannot be used to create a new table on a remote SQL Server instance, but remote source can be included in the Select statement, if there is a link to the remote instance.

If the source table contains any constraints, indexes, and triggers they will not be transferred to the new table. The column in the newly created table will not inherit the Identity property from the query output if the Select statement contains an aggregate function, the Join clause, or the Group by clause, and if the identity column is used in an expression, used more than once, or is from a remote data source.

Export and Import data by using ApexSQL Script

Using ApexSQL Script, a SQL Server database migration tool, make a SQL script to export data, or both data and structure, from a source instance and execute it on a destination instance to import data.

To script database data using ApexSQL Script, follow these steps:

Run ApexSQL Script
In the New project window, set the server, authentication way and connect to a database:
Depending on the structure on the destination instance, set the project options. If both, data and a source table are scripted, in the Options tab, under the Structure section, assuming that a destination database exists, uncheck the Create database option and the Script use for database option. If a table that contains constraints, indexes, or foreign keys is scripted, check the Names option to script names of those objects:
Under the Data section in the Options tab, check the Set identity insert on option, to handle loading data into tables that have Identity columns. Under the Script rows as section, select the Insert option:
If a destination database has a different structure of the source database, in the Options tab, under the Owners in the script section, select the Exclude owners option to exclude schemas from the object names:
Once everything is set, click the Load button in the bottom-right corner of the New project window
In the Results grid, after the Data button is clicked in the Home tab to show the Data grid view, filter columns to extract data from:
Switch to the Structure grid view, with a click on the Structure button from the Home tab, and select tables to script:
Click the Script button from the Home tab to start the Script wizard:
In the first step of the Script wizard, select the Structure and data scripting mode and click the Next button:
In the Output type step, select the SQL output type and click the Next button:
If there are any dependent objects, these will be shown and checked by default in the Dependencies step
In the last step, the SQL Script options step, select the Save the script to file option and click the Create button:
Open the created SQL script in SSMS and execute it on the same or a remote instance:

Copy data by using ApexSQL Data Diff

If a destination table already exists and it has the same structure as the source table, use ApexSQL Data Diff, a SQL data compare tool, that can copy data from one table or tables to a destination database table or tables.

To copy data with ApexSQL Data Diff, follow these steps:
1. Run ApexSQL Data Diff
2. In the New project window, click the New button:
3. Under the Data sources tab of the New project window, connect to the source and destination databases, and click the Compare button in the bottom-right corner:
4. In the Results grid, select tables and their corresponding rows to copy:
5. Click the Synchronize button to initiate the Synchronization wizard:
6. Go through the Synchronization wizard and under the Output options step, select the Create a synchronization script action along with the Save script to file option:
7. In the last step of the Synchronization wizard preview the impact of the synchronization script and review all warnings and actions:
8. Click the Create script button, open it in SSMS and execute the script:
Useful resources:
bcp Utility
SQL Server Import and Export Wizard
Bulk Import and Export of Data (SQL Server)
Related posts:
May 12, 2014