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, and 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 -> Export data command:

SQL Server Import and Export Wizard - selecting the Export data command

  1. In the Choose a Data source window, 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
  2. In the Choose a Destination window, 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:

    SQL Server Import and Export Wizard - Choose a Destination window

  3. In the Specify Table Copy or Query window, choose the Copy data from one or more tables or views option.
  4. Choose the tables and views and choose the Run immediately option, in the complete Wizard window:

    Dialog showing that the execution was successful

    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:

    View report dialog

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

Bcp utility - command to bulk export data to a txt file

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

Bcp utility - CLI command to insert data from a txt file

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 … SELECT * FROM OPENROWSET(BULK…)

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

When the INTO clause is used in a combination with the SELECT statement enables creating a new table based on the result set of a 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 start a new project dialog, connect to a database and depending on the structure on the destination instance, set the project options. If both, data and a source table are scripted, under the Structure script tab assuming that a destination database exists, uncheck the Script CREATE DATABASE option and the Script USE for a database option. If a table that contains constraints, indexes, or foreign keys is scripted, check the Script names option to script names of those objects:

ApexSQL Script - new project dialog, structure script tab

Under the Data script tab, select the Set IDENTITY_INSERT ON option, to handle loading data into tables that have IDENTITY columns. Under the Rows to script options select the Script rows as INSERT option:

Data script tab in ApexSQL Script

If a destination database has a different structure of the source database, under the Schema mapping tab, check the Exclude schemas option to exclude schemas from the object names and click the Open button:

Schema mappings tab in ApexSQL Script

In the Results grid, under the Data grid, filter columns to extract data from:

Data grid dialog - filtering columns to extract data from

From the Structure grid, select tables to script, and start the Script wizard:

Selecting tables to script, and starting the Script wizard

In the Script wizard, select the scripting mode and click the Next button:

Selecting the scripting mode in the Script Wizard

Save the script and execute it on the same or a remote instance:

Dialog showing the execution of saved SQL script

Copy data by using ApexSQL Data Diff

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

Start a new project, connect to the source and a destination databases, and click the Compare button:

In the Results grid, select tables to copy and click the Synchronize button:

ApexSQL Data Diff comparison results dialog - selecting tables to copy

In the Synchronization wizard you can see the impact of the synchronization script and review all warnings and actions:

Synchronization Wizard in ApexSQL Data Diff

Click the Create script button and execute the script:

Dialog showing the execution of created script

Useful resources:
bcp Utility
SQL Server Import and Export Wizard
Bulk Import and Export of Data (SQL Server)

May 12, 2014