BCP utility for SQL Server

BCP Utility for SQL Server

BCP (Bulk Copy Program) utility is a command-line tool provided by Microsoft SQL Server to import and export large amounts of data in and out of SQL Server databases quickly and efficiently. It can handle various file formats such as text, csv, and xml.

How to Use BCP Utility

Export Data

To export data using BCP utility, you can use the following command:

bcp <database_name>.<schema_name>.<table_name> out <output_file> -S <server_name> -U <username> -P <password> -c

• <database_name>: The name of the database you want to export data from.
• <schema_name>: The name of the schema of the table.
• <table_name>: The name of the table you want to export data from.
• <output_file>: The name of the file where the data will be exported.
• <server_name>: The name of the SQL Server instance.
• <username>: The username to connect to the SQL Server.
• <password>: The password to connect to the SQL Server.
• -c: Specifies that the data is in character format.

Import Data

To import data using BCP utility, you can use the following command:

bcp <database_name>.<schema_name>.<table_name> in <input_file> -S <server_name> -U <username> -P <password> -c

• <database_name>: The name of the database you want to import data into.
• <schema_name>: The name of the schema of the table.
• <table_name>: The name of the table you want to import data into.
• <input_file>: The name of the file from which the data will be imported.
• <server_name>: The name of the SQL Server instance.
• <username>: The username to connect to the SQL Server.
• <password>: The password to connect to the SQL Server.
• -c: Specifies that the data is in character format.

Example

Let's say we want to export data from a table called Employee in a database called CompanyDB to a file named EmployeeData.txt. We can use the following command:

bcp CompanyDB.dbo.Employee out EmployeeData.txt -S localhost -U sa -P password -c

And if we want to import data from a file named EmployeeData.txt into the Employee table in the CompanyDB database, we can use the following command:

bcp CompanyDB.dbo.Employee in EmployeeData.txt -S localhost -U sa -P password -c

Conclusion

BCP utility for SQL Server is a powerful tool that allows users to quickly and efficiently import and export large amounts of data. By using simple command-line commands, users can easily transfer data between SQL Server databases and external files. Whether you need to migrate data, create backups, or load data into a data warehouse, BCP utility can help you accomplish these tasks with ease.