Bulk data export / import (Draft)

This article explains how to perform bulk data export/import between SQL Server databases.

This process should be performed in NOT business hours.

TABLE EXPORT

1. Create directories

C:\Export

C:\Export\Data\

C:\Export\Data\Error\

C:\Export\Data\Log\

2. In the script bellow specify the correct db name instead of /SourceDBName/ and source server name instead of /ServerName/. In this example bcp utility connects to SQL Server with a trusted connection using integrated security.

Export entire table:
bcp SourceDBName.dbo.SourceTableName OUT C:\Export\Data\SourceTableName.bcp -m 1 -n -e C:\Export\Data\Error\Error_out.log -o C:\Export\Data\Log\Output_out.log -S[ServerName] -T
 
Export data from select statement(data file + format file):
--Export data
bcp "SELECT Name FROM SourceDBName.dbo.SourceTableName" queryout SourceTableName.dat -c -T -S[ServerName]
--Export format file
bcp SourceDBName.dbo.SourceTableName format nul -x -f SourceTableName.xml -T -Sint-ms\mssql2016 -c

3. Run CMD as Administrator and execute the script from step 2

bcp will create the following 

SourceTableName.bcp data file (C:\Export\Data\SourceTableName.bcp)

Error_out.log (C:\Export\Data\Error\Error_out.log)

Output_out.log (C:\Export\Data\Log\Output_out.log)

Review Error_out.log and Output_out.log.

Error_out.log should be blank.

DATA IMPORT

1. Create temporary table

SELECT * INTO Data_IMPORT
FROM TargetTableName
WHERE 1 = 2;

2. Run cmd. with the following script and specify the correct db name insted of TargetDName and server name instead of ServerName

1. Import entire table with bcp IN

bcp TargetTable IN C:\Export\Data\SourceTableName.bcp -b 5000 -h "TABLOCK" -m 1 -n -e C:\Export\Data\Error\Error_in.log -o C:\Export\Data\Log\Output_in.log -S[ServerName] -T -q

2. Import data from data file with INSERT INTO ... FROM OPENROWSET(BULK

INSERT INTO dbo.TargetTable
SELECT *
FROM OPENROWSET(BULK 'D:\Data\SourceTableName.txt',
    FORMATFILE = 'D:\Data\SourceTableName.xml') as t1;

Wait until the operation is done. It can take up to few hours depending on the amount of data you are exporting.

You can look through Output_out.log to understand the export status.

3. Insert into destination table

CREATE NONCLUSTERED INDEX IX_SourceImportTableName_Id
    ON SourceImportTableName(Id);
GO
 
SET NOCOUNT ON;
DECLARE @BatchSize INT = 1000;
DECLARE @I INT = 0;
--Calculate number of iterations
DECLARE @TotalCount INT = (SELECT COUNT(*) / @BatchSize
    FROM SourceImportTableName imp WITH(NOLOCK)
    WHERE NOT EXISTS(
       SELECT Id
       FROM TargetTableName l WITH(NOLOCK)
       WHERE l.Id = imp.Id));
WHILE (@I < @TotalCount)
BEGIN
INSERT INTO Lead WITH(TABLOCK)
SELECT TOP(@BatchSize) *
FROM SourceImportTableName imp
WHERE NOT EXISTS(
    SELECT Id
    FROM TargetTableName l
    WHERE l.Id = imp.Id)
SET @I = @I + 1;
END;

Here are example scripts to import Lead table on server int-ms\mssql2016

--Export
bcp SourceDBName.dbo.Lead OUT C:\Export\Data\Lead.bcp -m 1 -n -e C:\Export\Data\Error\Error_out.log -o C:\Export\Data\Log\Output_out.log -Sint-ms\mssql2016 -T
 
--Import
bcp TargetDBName.dbo.Lead IN C:\Export\Data\Lead.bcp -b 5000 -h "TABLOCK" -m 1 -n -e C:\Export\Data\Error\Error_in.log -o C:\Export\Data\Log\Output_in.log -Sint-ms\mssql2016 -T -q
 
--Insert data into destination table
USE TargetDBName;
CREATE NONCLUSTERED INDEX IX_Lead_Id
    ON Lead_IMPORT(Id);
GO
 
SET NOCOUNT ON;
DECLARE @BatchSize INT = 1000;
DECLARE @I INT = 0;
--Calculate number of iterations
DECLARE @TotalCount INT = (SELECT COUNT(*) / @BatchSize
    FROM Lead_IMPORT imp WITH(NOLOCK)
    WHERE NOT EXISTS(
       SELECT Id
       FROM Lead l WITH(NOLOCK)
       WHERE l.Id = imp.Id));
WHILE (@I < @TotalCount)
BEGIN
INSERT INTO Lead WITH(TABLOCK)
SELECT TOP(@BatchSize) *
FROM Lead_IMPORT imp
WHERE NOT EXISTS(
    SELECT Id
    FROM Lead l
    WHERE l.Id = imp.Id)
SET @I = @I + 1;
END;

Example: Import part of the bcp data file

--Export
bcp Azull_D_1.dbo.Account OUT C:\Export\Data\Account.bcp -m 1 -n -e C:\Export\Data\Error\Error_out.log -o C:\Export\Data\Log\Output_out.log -Sint-ms\mssql2016 -T
--Format file
bcp Azull_D_1.dbo.Account format nul -x -f Account.xml -T -Sint-ms\mssql2016 -m 1 -n
--Load data
INSERT INTO dbo.Account
SELECT *
FROM OPENROWSET(BULK 'D:\Data\Account.bcp',
    FORMATFILE = 'D:\Data\Account.xml') as t1
WHERE Createdon > '2017-08-01 00:00:00';

Example: Export/Import some Accounts from select statement

bcp "SELECT * FROM Azull_D_1.dbo.Account WHERE ModifiedOn > '2017-08-01 00:00:00'" queryout Account.txt -T -Sint-ms\mssql2016 -c
bcp Azull_D_1.dbo.Account format nul -x -f Account.xml -T -Sint-ms\mssql2016 -c
 
INSERT INTO dbo.Account
SELECT *
FROM OPENROWSET(BULK 'D:\Data\Account.txt',
    FORMATFILE = 'D:\Data\Account.xml') as t1;

Here is a link to full bcp utility documentation https://docs.microsoft.com/en-us/sql/tools/bcp-utility.

https://docs.microsoft.com/en-us/sql/relational-databases/import-export/create-a-format-file-sql-server