Lesson 5

Import and Export Practice Data with bcp

This example exports two practice rows and imports them into a separate empty table. Run the SQL in an authorized practice database, then run bcp in a Windows terminal. The examples are instructions, not commands executed by this website.

1. Prepare tables in SSMS

CREATE TABLE dbo.BcpSource (EmployeeID int NOT NULL PRIMARY KEY, EmployeeName varchar(50) NOT NULL);
CREATE TABLE dbo.BcpTarget (EmployeeID int NOT NULL PRIMARY KEY, EmployeeName varchar(50) NOT NULL);
INSERT INTO dbo.BcpSource (EmployeeID, EmployeeName)
VALUES (1, 'Avery'), (2, 'Morgan');

Use a practice database named TransferLab for the commands below, or replace that name consistently. Create these objects only when the names are available. The two columns deliberately have matching order and types; the sample names contain no tabs or newlines.

2. Export from a Windows terminal

bcp "SELECT EmployeeID, EmployeeName FROM TransferLab.dbo.BcpSource ORDER BY EmployeeID" queryout "C:\data\employees.tsv" -S SERVER -T -c

Replace SERVER with your instance name and create C:\data first. The file is created on the machine running bcp. Character mode defaults to tab-separated fields and newline row terminators; this example uses the Windows utility consistently for both directions.

3. Import into the empty target

bcp TransferLab.dbo.BcpTarget in "C:\data\employees.tsv" -S SERVER -T -c

Use an account with the required target permissions. Re-running the import against an already loaded target can cause duplicate-key errors. Inspect the current state before retrying instead of deleting data automatically.

4. Validate in SSMS

SELECT EmployeeID, EmployeeName FROM dbo.BcpTarget ORDER BY EmployeeID;
SELECT COUNT_BIG(*) AS ImportedRows FROM dbo.BcpTarget;

Expect two rows: 1/Avery and 2/Morgan. For a real transfer, compare keys, rejected rows, and meaningful totals as well as counts. A count alone cannot prove that every field is correct.

Loading and recovery

Bulk imports can consume substantial log space. Minimal logging has specific prerequisites and does not mean zero logging. Batch sizes change transaction boundaries; earlier committed batches may remain after a later failure. Design a retry strategy before loading production data.

Practice writing a bcp import command.