🧠 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,50000
102,Jane,Smith,HR,48000
103,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:
IN→ import data-t,→ fields separated by commas-c→ text format-T→ Windows authentication
🧰 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.
📚 Official References
✅ 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!
