When it comes to handling large volumes of data in SQL Server—especially importing and exporting flat files like CSVs or TXT files—the BCP (Bulk Copy Program) utility is one of the most powerful tools in your toolkit. Whether you’re a DBA, developer, or data engineer, mastering BCP can save you hours of work.
In this post, we’ll walk through what BCP is, how to install it, use it, and troubleshoot common errors with real-world examples.
🧰 What is BCP?
BCP stands for Bulk Copy Program. It’s a command-line utility provided by Microsoft to quickly import data into SQL Server tables or export data from them.
🔑 Key Use Cases:
-
Export large tables to
.txtor.csvfiles -
Import external data into SQL Server
-
Generate format files for complex table structures
-
Automate data loads via scripts
🛠️ How to Install BCP
BCP comes with Microsoft SQL Server and the ODBC driver. If it’s not already installed, here’s how to get it:
✅ Steps:
-
Install SQL Server Command Line Utilities
-
Download from: https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility
-
-
Ensure ODBC Driver 17+ is Installed
-
Open Command Prompt and test:
bcp /?
-
You should see the help guide. That means it’s installed!
💡 BCP Command Syntax
bcp {database.schema.table | query} {in | out | queryout | format} data_file
-S server_name
-U username
-P password
[-c | -n | -w | -t field_terminator | -r row_terminator]
Let’s break it down:
| Option | Description |
|---|---|
in |
Import data into a table |
out |
Export data from a table |
queryout |
Export using a custom SELECT query |
-c |
Character mode (default text format) |
-t |
Field terminator (default is tab) |
-r |
Row terminator (default is newline) |
-S |
SQL Server name |
-U/-P |
SQL Server credentials |
-T |
Use Windows Authentication |
🧪 Real-World BCP Examples
1️⃣ Export Table to File
bcp MyDatabase.dbo.Customers out “C:\exports\customers.csv” -c -t, -S localhost -U sa -P MyPassword
📌 Explanation:
-
out: we’re exporting -
-c: use character format -
-t,: comma-separated fields
2️⃣ Import Data into a Table
bcp MyDatabase.dbo.Employees in “C:\imports\employee.csv” -c -t, -S localhost -U sa -P MyPassword
👉 Ensure the number and order of columns in the file match the table schema.
3️⃣ Export with a Custom Query
bcp “SELECT Name, Email FROM MyDatabase.dbo.Users WHERE Active = 1” queryout “C:\exports\active_users.csv” -c -t, -S localhost -U sa -P MyPassword
🧠 Note: The query must be in quotes and you should NOT specify the database in the query—use -d for that.
🔐 Authentication Modes
| Mode | Command |
|---|---|
| SQL Auth | -U sa -P password |
| Windows Auth | -T (no username/password required) |
Example:
bcp MyDB.dbo.Logs out “C:\logs.csv” -c -t, -S MyServer -T
📁 File Format Options
| Option | Format |
|---|---|
-c |
Character (text, default) |
-n |
Native (binary) |
-w |
Unicode (UTF-16) |
-t |
Field delimiter |
-r |
Row delimiter (\n default) |
🧽 Best Practices & Tips
-
Avoid inserting into IDENTITY columns unless absolutely needed.
-
Use staging tables if your source data doesn’t perfectly match the destination schema.
-
Check permissions on files and folders before running BCP.
-
Avoid relative paths—always use full paths.
-
If data structure is complex, use a format file to define mappings:
bcp MyTable format nul -c -x -f mytable_format.xml -S server -U sa -P pass
📚 Want to Learn More?
-
Run
bcp /?to see all available options
✅ Final Thoughts
BCP is a lightning-fast way to move data in and out of SQL Server. Whether you’re dealing with large datasets or building automated ETL pipelines, knowing how to leverage BCP gives you a serious performance edge.
🛠 Need help with BCP in your environment? Drop a comment or contact us — we’d love to help you troubleshoot!