SQL Server BCP reference

Tuesday, 11 August 2020 - 1 minute to read

bcp Database.dbo.TableName out savefile.bcp -S server -T -N -k

Exports data from a certain server and table to a savefile. -T means trusted connection (using Windows authentication), -N uses the database's native storage with Unicode values (specifically designed for server-to-server copy), and -k means retain null values.

bcp Database.dbo.TableName in savefile.bcp -S localhost -U sa -P password -N -k

Importa data to localhost from a savefile. -U/-P is the username/password pair.

Other options:

  • -E means that identity values in the data is supposed to be used for identity columns. If not specified, new identity values will be generated.

The recommendation is to make batch files for common operations (bcp-prod-out, bcp-local-in, bcp-uat-in etc) so it's difficult to overwrite production data by mistake.

Add a comment

  to leave a comment. It's quick and painless.