What is BCP Utility in SQL server?

Introduction BCP Utility:

The bcp utility bulk copies data between an instance of Microsoft SQL Server 2005 and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns.

Syntax:

bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
    {in | out | queryout | format} data_file
    [-mmax_errors] [-fformat_file] [-x] [-eerr_file]
    [-Ffirst_row] [-Llast_row] [-bbatch_size]
    [-n] [-c] [-w] [-N] [-V (60 | 65 | 70 | 80)] [-6]
    [-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term]
    [-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
    [-Sserver_name[instance_name]] [-Ulogin_id] [-Ppassword]
    [-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]

Examples:

Copying Table Rows into a Data File (with a Trusted Connection)

The following example illustrates the out option on the AdventureWorks.Sales.Currency table. This example creates a data file named Currency.dat and copies the table data into it using character format. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.

At a command prompt, enter the following command:

bcp AdventureWorks.Sales.Currency out Currency.dat -T -c

Copying Table Rows into a Data File (with Mixed-Mode Authentication)

The following example illustrates the out option on the AdventureWorks.Sales.Currency table. This example creates a data file named Currency.dat and copies the table data into it using character format.

he example assumes that you are using mixed-mode authentication, you must use the -U switch to specify your login ID. Also, unless you are connecting to the default instance of SQL Server on the local computer, use the -S switch to specify the system name and, optionally, an instance name.

bcp AdventureWorks.Sales.Currency out 
Currency.dat -c -U<login_id> -S<server_nameinstance_name>

The system will prompt you for your password.

Copying Data from a File to a Table

The following example illustrates the in option by using the file created in the preceding example (Currency.dat). First, however, this example creates an empty copy of the AdventureWorks Sales.Currency table, Sales.Currency2, into which the data is copied. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.

To create the empty table, in Query Editor, enter the following command:

USE AdventureWorks;
GO
SELECT * INTO AdventureWorks.Sales.Currency2
FROM AdventureWorks.Sales.Currency WHERE 1=2

To bulk copy the character data into the new table–that is, to import the data–enter the following command at a command prompt:

bcp AdventureWorks.Sales.Currency2 
in Currency.dat -T -c

To verify that the command succeeded, display the contents of the table in Query Editor, and enter

USE AdventureWorks;
GO
SELECT * FROM Sales.Currency2

Copying a Specific Column into a Data File

To copy a specific column, you can use the queryout option. The following example copies only the Name column of the Sales.Currency table into a data file. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.

At the Windows command prompt, enter:

bcp "SELECT Name FROM AdventureWorks.Sales.Currency" 
queryout Currency.Name.dat -T -c

Copying Data From a Query to a Data File

To copy the result set from a Transact-SQL statement to a data file, use the queryout option. The following example copies the names from the AdventureWorks.Person.Contact table, ordered by last name then first name, into the Contacts.txt data file. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.

At the Windows command prompt, enter:

bcp "SELECT FirstName, LastName FROM 
AdventureWorks.Person.Contact 
ORDER BY LastName, Firstname" 
queryout Contacts.txt -c -T

Creating a Non-XML Format File

a non-XML format file, Currency.fmt, for the Sales.Currency table in the AdventureWorks database. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.

At the Windows command prompt, enter:

bcp AdventureWorks.Sales.Currency 
format nul -T -c  -f Currency.fmt

Creating an XML Format File

The following example creates an XML format file named Currency.xml for the Sales.Currency table in the AdventureWorks database. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.

At the Windows command prompt, enter:

bcp AdventureWorks.Sales.Currency 
format nul -T -c -x -f Currency.xml

Using a Format File to Bulk Import with bcp

To use a previously created format file when importing data into an instance of SQL Server, use the -f switch with the in option. For example, the following command bulk copies the contents of a data file, Currency.dat, into a copy of the Sales.Currency table (Sales.Currency2) by using the previously created format file (Currency.xml). The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.

At the Windows command prompt, enter:

bcp AdventureWorks.Sales.Currency2 
in Currency.dat -T -f Currency.xml