BCP is a utility that is installed by default with SQL Server editions and is used for bulk import or export of a large volume of data in user-defined formats. This utility is optimized for large data transfers either between SQL Server instances or between a SQL instance and a text file. The utility is used for both one-time data import/export and regular data loads within ETL processes. The utility is executed via the command-line (cmd) and has its own syntax.
BCP Syntax
You can easily view the utility’s parameters. Open the command prompt and type “bcp.” This will display a list of parameters that you can apply. Some of the most important bcp arguments include:
- -S … server name (the server to which you are connecting)
- -U … user name (the login under which you are connecting)
- -P … password (the password for the login)
- -d … database (the database to which you are connecting)
- -n … native database data format
Data Export Syntax: Exporting data from a table to a file
- [database_name.] schema.table_name | view_name | out data_file | format nul} – if exporting an entire table
- query | queryout data_file | format nul} – if exporting based on a query
Data Import Syntax: Importing data into a table from a file
- [database_name].schema.table_name | view_name | in data_file | format nul}
Example of Exporting/Importing SQL Data Using BCP – Bulk Copy Program
Let’s demonstrate how to export data from a table.
1) Preparing the Table – Create a simple table with 2 columns and populate it with data. The first column is a primary key, and the second column contains a GUID replicated 4 times. The second column has a text string of 148 characters on each row. In total, we generate 100,000 records.
CREATE TABLE Temp.dbo.Test (ID INT IDENTITY(1,1) PRIMARY KEY,
Textove_pole VARCHAR(255)
)
DECLARE @Iterator INT=1
DECLARE @GUID UNIQUEIDENTIFIER
START_CYCLE:
SET @GUID= NEWID()
INSERT INTO Temp.dbo.Test (Text_column)
SELECT REPLICATE(CONCAT(CONVERT(VARCHAR(255),@GUID),‘-‘),4)
SET @Iterator=@Iterator+1
IF @Iterator <= 100000 GOTO START_CYCLE
2) The destination directory for data export will be: C:\Destination_folder_bcp
3) The BCP command will look like this: [Temp].[dbo].[Test] out “C:\Cilovy_folder_bcp\Temp_dbo_Test_2018_07_02.bcp” -SSQL_INSTANCE -T -n
4) Result: Done. Alternatively, perform the import
For more information on the Microsoft documentation for the bulk copy program utility, click here.