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.
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:
- 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
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:
- In the Specify Table Copy or Query window, choose the Copy data from one or more tables or views option.
Choose the tables and views and choose the Run immediately option, in the complete Wizard window:
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
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 … 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:
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:
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:
In the Results grid, under the Data grid, filter columns to extract data from:
From the Structure grid, select tables to script, and start the Script wizard:
In the Script wizard, select the scripting mode and click the Next button:
Save the script 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, 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:
In the Synchronization wizard you can see the impact of the synchronization script and review all warnings and actions:
Click the Create script button and execute the script:
May 12, 2014