Skip to content
DBAExperts
SQL Server Migration & data

How to export and import data in SQL Server (BCP and BULK INSERT)

To move large volumes of data, BCP (command line) and BULK INSERT (T-SQL) are the fastest tools. Here is how to use each one.

  1. 1

    Export data with BCP (out)

    BCP is a command-line utility. With 'out' it exports a table or view to a file. -c uses character text format, and -t sets the delimiter.

    bcp MiBase.dbo.Ventas out "D:\Export\ventas.csv" ^
  -c -t"," -r"\n" -S SRV1\INST -T
  2. 2

    Export the result of a query (queryout)

    With 'queryout' you can export any SELECT, not just a full table. Handy for filtering or joining before exporting.

    bcp "SELECT ClienteID, Total FROM MiBase.dbo.Ventas WHERE Total > 1000" ^
  queryout "D:\Export\ventas_grandes.csv" ^
  -c -t"," -S SRV1\INST -T
  3. 3

    Import with BULK INSERT from T-SQL

    BULK INSERT loads a file into an existing table from inside SQL Server. The file must be accessible to the SQL Server service account.

    BULK INSERT dbo.Ventas
FROM 'D:\Import\ventas.csv'
WITH (
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\n',
  FIRSTROW = 2,        -- salta el encabezado
  TABLOCK,             -- mejora rendimiento
  KEEPNULLS
);
  4. 4

    Import with BCP (in)

    BCP also imports with 'in'. Handy when the file is on the client machine and not on the server.

    bcp MiBase.dbo.Ventas in "D:\Import\ventas.csv" ^
  -c -t"," -F 2 -S SRV1\INST -T

// common mistake

BULK INSERT reads the file from the server (the SQL service account must have access to that path), not from your machine. Watch the ROWTERMINATOR: Windows files use \r\n, not just \n. For very large volumes, use TABLOCK and consider the BULK_LOGGED model to reduce log growth.

// when it's worth an expert

Migrations and bulk loads have fine details: encoding, formats, data types, referential integrity, and performance. At dba.mx we do migrations and data loads verified row by row, at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.