Loading Data with BCP


BCP or Bulk Copy Program is one of oldest part of SQL Server. BCP is a lightweight software used to import/export data between the SQL Server instances quickly. If you ever want to backup a single table in SQL Server, I think, you might have a look at BCP. Though it is a very useful tool, it’s switches makes it a little complicated. In this post, we are going to look at some of the most commonly used switches in BCP and its usage. The script to generate the database and the data will be available for download, click here to download the files.

The general syntax of BCP along with all available switches are as follows:

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
[-d database name]

Now we are going to use some of these switches. For all these demos, we are going to use the CodeTest database on our test instance.

Creating a format file with BCP

To create a native format file for the table dbo.Friends in the CodeTest database, use the following command:

bcp CodeTest.dbo.Friends format nul -c -f Friends.fmt -T

where -c = character type
-f = format file name
-T = Trusted Connection

You can also generate an XML formatted file for the BCP operation. The syntax for creating an XML format file is as follows:

bcp codetest.dbo.Friends format nul -c -x -f Friends.xml -T
-x = generate XML based Format file

Importing Data Using Format File

Since we have created the format file, we are going to insert data into the table using the format file. We will insert data using both native format file and XML based format file:

Using Native Format File:-

Using the native format file, Friends.fmt, we are going to upload data into the dbo.Friends table of CodeTest database. We will upload the data from Friends1.txt file.

c:\Temp\BCPTest>bcp codetest.dbo.friends in Friends1.txt -f Friends.fmt -T

Starting copy...

4 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 60 Average : (66.67 rows per sec.)

For the above example, I have kept the Friends1.txt file in the same location as the format file. If your files are placed in a different location, please provide the absolute path of the file. The output of the Select query on dbo.Friends table gives the following result:

image

Using XML Format File:-

We will insert the data from Friends2.txt to the table dbo.Friends using XML based format file. The syntax to upload the data from Friends2.txt to the table dbo.Friends is as follows:

c:\Temp\BCPTest>bcp codetest.dbo.friends in Friends2.txt -f Friends.xml -T

Starting copy...

4 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 10 Average : (400.00 rows per sec.)

We have inserted another four rows in to the dbo.Friends table in CodeTest database. Lets run a select query on the table and observe the output.

image

Exporting Data Using Format File

As import, we can export data from a table using BCP with/without using format file. In this section, we are going to export the data using the format file. For simplicity, I am going to use the XML format file to export the data from dbo.Friends table. The syntax for the code is as follows:

c:\Temp\BCPTest>bcp codetest.dbo.friends out Friends3.txt -f Friends.xml -T

Starting copy...

8 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (8000.00 rows per sec.)

The file Friends3.txt contains the following:

imageSometimes, its required to save an output of a certain query. BCP also helps in saving the output of a given query. In the below example, we are going to export data to a file from dbo.Friends table based on a certain query, using the XML based format file. The syntax is as given below:

c:\Temp\BCPTest>bcp "select FriendID, FriendName, DOB from codetest.dbo.friends
where friendid = 8" queryout Friends4.txt -f Friends.xml -T

Starting copy...

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 10 Average : (100.00 rows per sec.)

All the above demos are created using a Trusted Authentication on a default SQL Server instance. However, you can also perform the same operations on a named instance using SQL Server authentication. In the below example, we are going to create another format file using SQL Server Authentication:

c:\Temp\BCPTest>bcp codetest.dbo.friends format nul -c -x -f SQLFormat.xml -S BLRDB04 -U sudeepta -P P@$$word

In the above example, the switch –S represents the server name (if you have a named instance, then you need to add the named instance name after the –S switch, e.g. –S [ServerName\InstanceName]), –U represents the UserName, and –P represents the password for the user account. To execute the above command, you should have permission on the table.

For a more detailed information on BCP syntax and its usage, please visit the following MSDN website: http://msdn.microsoft.com/en-us/library/ms162802.aspx

Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in SQL Server Tools. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s