How to import and export bulk data using Bulk Copy Program

BCP means bulk copy program, is a simple command prompt utility in SQL server. It is a very strong tool by which you can import and export millions of records from text files or DAT files to SQL server or vice versa.

It is a powerful command-line utility, which is very fast in operation and can save lots of time for processing as compared to other method of bulk import and export of data

BCP tool is installed by default in SQL Server. This tool is usually installed in the given below path.

Drive:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe

Before getting started, we need to know some possible arguments that can be used.

Open windows command prompt and type BCP or BCP /? which is bulk copy program and enter.

Now you can see the possible parameters of the bulk copy program.

Step1: Export data with trusted connection:

Now we are going to export data from SQL server table to file.

To export data we need to use below syntax:

BCP BCP_Test.dbo.EMP OUT D:\BCP\EXPORT\ExportDatatoFile.txt -T -c

BCP_Test defines Database Name

dbo defines Schema Name

EMP defines Table Name

OUT Defines Export data

D:\BCP\EXPORT\ExportDatatoFile.txt defines the path file name

-T defines Trusted User (Windows Authentication)

-c defines Character Type.

Now we need to run above syntax in Command Prompt.

After the syntax is executed successfully. We will receive the number of rows copied, the network packet size and the speed to copy the rows per second.

If everything is ok, the file will be created in mentioned path as shown below.

And we can view the data by opening the file.

Step2: Export Data with Mixed Mode Authentication:

Now we are trying to export data from SQL Server to file by using mixed mode authentication.

Use below syntax for mixed mode authentication.

Bcp BCP_Tesst.dbo.EMP OUT D:\BCP\EXPORT\ExportDatatoFile_MixedModeAuth.txt -SDESKTOP-5L4ML7G -Usa -Plogin@123 -c

Here S defines Server Name

U defines User Name

P defines Password

Step3: Import Data with Trusted Connection:

Before importing data from file to SQL Server there is no data in particular table.

Now, import the data using below syntax.

Bcp BCP_Tesst.dbo.SALGRADE IN D:\BCP\IMPORT\ImportDatatoSQLServer.txt -T -c

Now data is imported to SQL Server table.

Step4: Import Data with Mixed Mode Connection:

Now we are importing the data using mixed mode authentication.

Use the below syntax for mixed mode authentication.

BCP BCP_Tesst.dbo.SALGRADE IN D:\BCP\IMPORT\ImportDatatoSQLServer.txt -SDESKTOP-5L4ML7G -Usa -Plogin@123 -c

Step5: Export Data with Specific Column:

Now we are using query-out option to export data from particular column.

BCP "SELECT DEPTNO, DNAME from BCP_Tesst.dbo.DEPT" queryout D:\BCP\QUERYOUT\QueryOutColumn.txt -T -c

Now we can see the select columns output result.

Step6: Export Data with Specific Rows:

Now we exporting for specific rows.

BCP "SELECT * from BCP_Tesst.dbo.DEPT where DEPTNO = 50" queryout D:\BCP\QUERYOUT\QueryOutRows.txt -T -c

Now we can see the select rows output result.

Step7: Create XML File Format:

Now we are going create XML file format using below syntax.

Now directly you won't get any information. We can directly go to path and see the XML file format generated as shown in below figure.

CONCLUSION:

It's very difficult to manage the export and import operations on bulk data within a time frame. In order to overcome this problem BCP (Bulk Copy Program) plays vital role to reduce time and we can transfer the bulk data from source to destination.

Further reading

If you found this article helpful, you might want to check out these related resources,

• How to upload PDFs in to SQL Server
• Top 5 Cloud Databases of 2022