The general syntax of BCP along with all available switches are as follows:
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:
You can also generate an XML formatted file for the BCP operation. The syntax for creating an XML format file is as follows:
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.
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:
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:
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.
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:
The file Friends3.txt contains the following:
Sometimes, 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:
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:
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