How to import or export data using BCP in SQL Server?
BCP (Bulk Copy Program): - The Bulk Copy Program (BCP) utility in SQL
Server enables you to import bulk data into a table or export it
from a table into a file. It also supports several options that define how data
will be exported, where it will be imported and which data will be loaded.
The BCP utility (Bcp.exe) is a command-line tool that uses the Bulk Copy Program
(BCP) API.
The basic syntax for using BCP is:
bcp
Now, let's see what exactly does the syntax means.
1. <table_name> as the name indicates that it will take table.
For example, you might use the Customer.dbo.CustomerDetails to insert record
into the database owner Customer DataBase.
2. <direction> direction indicates whether you want to import or export
data.
For example, if you want to import data then you need to specify ("in"
direction) and if you want to export data then you need to specify ("out"
direction).
3. <file_name> file_name is the full path to the file.
For example, you could import the file
C:\CustomerDetails\invoice.txt.
4. <options> options allow you to specify parameters for the bulk
operation.
The following tasks are performed by the BCP:
1. Bulk exports data from a SQL Server table into a data file.
2. Bulk exports data from a query.
3. Bulk imports data from a data file into a SQL Server table.
4. Generates format files.
Now, let's see a small on demonstration on how exactly we can import and export
data in SQL server using BCP (Bulk Copy Program).
Let's first see how exactly we can export the data of the table using
BCP (Bulk Copy Program).
Consider that we have the table of CustomerDetails like below diagram.
To export the data using BCP (Bulk Copy Program) we have to use
windows command prompt, to open windows command prompt Click > Start >
Run > cmd, as soon as you type cmd and click ok then the Command prompt
window will open like below diagram.
Now, just add the below command line to your command prompt to export the data.
Now, just press enter if everything goes right you will see the message like
below diagram.
Similarly, you can perform importing data using BCP (Bulk Copy Program).
Do not forget to see our SQL Server interview question videos by clicking on
Sql Server Interview Question