🧠 What is BCP?
BCP (Bulk Copy Program) is a command-line utility provided by Microsoft SQL Server that allows you to import and export large volumes of data between a SQL Server database and a data file.
It’s extremely useful for:
Migrating data between servers
Exporting data for reporting or backups
Loading CSV or flat files into SQL Server
BCP is fast, lightweight, and works without needing SQL Server Management Studio (SSMS).
🧩 Basic Syntax
bcp {database_name}.{schema}.{table_name} [option]
Example
bcp AdventureWorks2022.dbo.Person OUT "C:\Export\PersonData.txt" -S DESKTOP-12345 -T -c
🧾 Parameters Explained
| Parameter | Description |
|---|
bcp | Launches the Bulk Copy Program |
{database}.{schema}.{table} | Specifies the full table name |
OUT / IN | Direction — OUT for export, IN for import |
"C:\path\file.txt" | Path to the data file |
-S | SQL Server instance name |
-T | Use Windows Authentication |
-U / -P | Use SQL Authentication (-U username -P password) |
-c | Character mode (text format) |
-n | Native mode (binary format, faster) |
-t | Field terminator (e.g. -t, for CSV) |
-r | Row terminator (default \n) |
🧮 Example 1: Export SQL Table to Text File
Let’s export a table named Employees from the database HRDB.
bcp HRDB.dbo.Employees OUT "C:\Data\employees.txt" -S DESKTOP-SQL01 -T -c
✅ Result
This command exports the contents of the Employees table into a plain text file using Windows authentication and character format.
📥 Example 2: Import Data from Text File into SQL Table
Suppose you have a CSV file named employees.csv with data like this:
101,John,Doe,IT,50000102,Jane,Smith,HR,48000103,Mark,Taylor,Finance,53000
You can import it into a SQL Server table Employees using:
bcp HRDB.dbo.Employees IN "C:\Data\employees.csv" -S DESKTOP-SQL01 -T -c -t,
✅ Explanation
🧰 Example 3: Export Query Results (Not Entire Table)
BCP can also export data from a custom query.
bcp "SELECT FirstName, LastName, Department FROM HRDB.dbo.Employees WHERE Department='IT'" queryout "C:\Data\ITEmployees.txt" -S DESKTOP-SQL01 -T -c
✅ Note:
You must include queryout when using a SQL query.
🔐 Example 4: Use SQL Authentication
If your SQL Server doesn’t allow Windows Authentication:
bcp HRDB.dbo.SalaryDetails OUT "C:\Export\Salary.txt" -S DESKTOP-SQL01 -U sa -P StrongPassword123 -c
📊 Example 5: Import Data with Custom Terminators
If your file uses semicolon (;) as a separator:
104;Amit;Verma;Operations;47000
Then use:
bcp HRDB.dbo.Employees IN "C:\Data\employees_semicolon.csv" -S DESKTOP-SQL01 -T -c -t";"
🧾 Example 6: Create a Format File
When importing or exporting complex data types, it’s better to use a format file (.fmt).
Step 1. Generate a format file
bcp HRDB.dbo.Employees format nul -S DESKTOP-SQL01 -T -c -f "C:\Data\employee_fmt.fmt"
Step 2. Import using format file
bcp HRDB.dbo.Employees IN "C:\Data\employees.txt" -S DESKTOP-SQL01 -T -f "C:\Data\employee_fmt.fmt"
✅ Why use format files?
They make imports consistent and reusable, especially when working with files of varying column orders.
⚡ Example 7: Export with Headers (using SQLCMD workaround)
BCP does not export headers by default.
To include headers, combine with SQLCMD:
sqlcmd -S DESKTOP-SQL01 -d HRDB -E -Q "SET NOCOUNT ON; SELECT 'EmpID','FirstName','LastName','Dept','Salary' UNION ALL SELECT CAST(EmpID AS NVARCHAR), FirstName, LastName, Dept, CAST(Salary AS NVARCHAR) FROM dbo.Employees" -o "C:\Data\EmployeesWithHeader.csv" -h-1 -s"," -W
🧹 Example 8: Export Data to Azure SQL Database
You can use the same command, just replace server name:
bcp AzureDB.dbo.Products OUT "C:\Data\products.csv" -S myserver.database.windows.net -U myuser -P mypassword -c
🧾 Common Errors & Solutions
| Error Message | Possible Cause | Fix |
|---|
Unable to open BCP host data-file | Wrong file path | Ensure correct directory and permissions |
Invalid object name | Wrong database/schema | Check database and schema name |
String data, right truncation | Column too small | Increase SQL column size |
Unexpected EOF | Wrong terminators | Check -t and -r options |
🧠 Tips & Best Practices
✅ Use -b option to set batch size (for large imports):
-b 5000
Processes 5,000 rows per batch for better performance.
✅ Use -e to log errors:
-e "C:\Logs\bcp_errors.txt"
✅ Combine BCP with PowerShell or batch scripts for automation.
✅ Conclusion
The BCP (Bulk Copy Program) utility is one of the most efficient tools for handling large-scale data imports and exports in SQL Server.
It’s simple, scriptable, and ideal for:
Data warehousing
ETL operations
Database migrations
Once you master BCP with its parameters and format options, you can move millions of records within seconds!