Click here to monitor SSC
  • Av rating:
  • Total votes: 116
  • Total comments: 21
Robert Sheldon

Working with the bcp Command-line Utility

10 December 2009

Even though there are many other ways to get data into a database, nothing works quite as fast as BCP, once it is set up with the right parameters and format file. Despite its usefulness, the art of using the command-line utility has always seemed more magic than method; but now along comes Robert Sheldon to shed light on the murky details.

The bcp utility is a command-line tool that uses the Bulk Copy Program (BCP) API to bulk copy data between an instance of SQL Server and a data file. By using the utility, you can export data from a SQL Server database into a data file, import data from a data file into a SQL Server database, and generate format files that support importing and exporting operations.

To use the bcp utility to perform these tasks, you can run a bcp command (along with the appropriate arguments) at a Command Prompt window. The command should conform to the following syntax:>

bcp {table|view|"query"}
    {out|queryout|in|format}
    {data_file|nul}
    {[optional_argument]...}

As you can see, a bcp command requires three arguments. The first (table|view|"query") represents the data source or destination in a SQL Server database. You can use the bcp utility to export data from a table or view or through a query. If you specify a query, you must enclose it in quotation marks. In addition, you can import data into a table or view. If you import into a view, all columns within the view must reference a single table. (Note that, when you specify a table or view, you must qualify the name with the database or schema names as necessary.)

The second argument in a bcp command (out|queryout|in|format) determines the command’s mode (direction). When you run a bcp command, you must specify one of the following four modes:

  • out: The command exports data from a table or view into a data file.
  • queryout: The command exports data retrieved through a query into a data file.
  • in: The command imports data from a data file into a table or view.
  • format: The command creates a format file based on a table or view. (Format files are explained later in the article.)

The third argument in a bcp command (data_file|nul) is the full path of the data file or, when a data file should not be specified, the nul value. If you’re importing data, you must specify the file that contains the source data. If you’re exporting data, you must specify the file that the data will be copied to. (If the file does not exist, it will be created.) When you’re using the bcp utility to generate a format file, you do not specify a data file. Instead, you should specify nul in place of the data file name.

In addition to the three required arguments, you can include one or more optional arguments when you issue a bcp command. The bcp utility supports numerous optional arguments, and the ones you include often depend on the mode you specify in the second argument. The remainder of this article provides examples that demonstrate how many of these arguments work. For a description of all the arguments supported by the bcp utility, see the topic “bcp Utility” in SQL Server Books Online.

Exporting Data from a Table or View

As mentioned above, when you export data out of a table or view, you must specify the out option, along with the data source and destination file. The following bcp command copies data from the Employee table in the AdventureWorks2008 sample database and copies it to the EmployeeData.dat file.

bcp AdventureWorks2008.HumanResources.Employee out C:\Data\EmployeeData.dat -S localhost\SqlSrv2008 -T

As you would expect, the command includes the three required arguments: the source table (AdventureWorks2008.HumanResources.Employee), the mode (out), and the full path name of the destination data file (C:\Data\EmployeeData.dat). If the data file exists when you run the command, any data within the file will be overwritten with the exported Employee information. If the file does not exist, it will be created and the data will be exported.

The fourth argument in the example (-S localhost\SqlSrv2008) specifies the server and instance of SQL Server. You do not have to include the instance name if the source database is in the default instance. And if the source database is in the default instance on the local machine, you do not have to specify the -S argument at all, as in the following example:

bcp AdventureWorks2008.HumanResources.Employee out C:\Data\EmployeeData.dat -T

The last argument in the preceding examples (-T) indicates that a trusted connection should be used to connect to SQL Server. You should use this option if the SQL Server instance uses integrated security. If integrated security is not used, you should instead specify the -U argument, along with the login ID of an account that can access the SQL Server instance. For example, the following bcp command specifies the login ID acct1 when accessing the SQL Server instance:

bcp AdventureWorks2008.HumanResources.Employee out C:\Data\EmployeeData.dat -S localhost\SqlSrv2008 –Uacct1

When you run this command, you will be prompted for a password. Alternatively, you can include the password in the command as well by specifying the -P argument, along with the account’s password (pw123), as shown in the following example:

bcp AdventureWorks2008.HumanResources.Employee out C:\Data\EmployeeData.dat -S localhost\SqlSrv2008 –Uacct1 –Ppw123

However, Microsoft generally recommends that you do not include the -P argument and instead wait to be prompted. But there might be circumstance when you want to pass in the password as part of the command.

When you run any of the commands shown in the preceding examples, you will be prompted for information about each column in the source table or view. The following three prompts show you the type of data that you need to supply for each column:

Enter the file storage type of field BusinessEntityID [int]:
Enter prefix-length of field BusinessEntityID [0]:
Enter field terminator [none]:

These prompts are what you receive for the BusinessEntityID column in the Employee table. Notice that each prompt includes a recommended value, shown in the brackets. To accept the suggested setting, press Enter after you receive the prompt. Otherwise enter a value and then press Enter. Note, however, the recommended settings are generally your best options. (For more information about each prompt, see the topic “Specifying Data Formats for Compatibility by Using bcp” in SQL Server Books Online.)

After you respond to each prompt for each column, you will be asked whether you want to save the format information and, if so, the full path name of the format file, as shown in the following two prompts:

Do you want to save this format information in a file? [Y/n]
Host filename [bcp.fmt]:

As you can imagine, it can get quite annoying having to supply format information each time you run a bcp command when exporting data. Fortunately, the bcp utility includes options that make this process much simpler. When defining your bcp command, you can include one of the following four arguments, which specify how the data should be formatted:

  • -n (native format): The bcp utility retains the database native data types when bulk copying the data to the data file. Microsoft recommends that you use this format to bulk copy data between instances of SQL Server. However, you should use this format option only when the data file should not support extended or double-byte character set (DBCS) characters.
  • -N (Unicode native format): The bcp utility uses the database native data types for non-character data and uses Unicode for character data for the bulk copy operation. Microsoft recommends that you use this format to bulk copy data between SQL Server instances when the data file should support extended or DBCS characters.
  • -w (Unicode character format): The bcp utility uses Unicode characters when bulk copying data to the data file. This format option is intended for bulk copying data between SQL Server instances. Note, however, that the Unicode native format (-N) offers a higher performance alternative.
  • -c (character format): The bcp utility uses character data for the bulk copy operation. Microsoft recommends that you use this format to bulk copy data between SQL Server and other applications, such as Microsoft Excel.

When you include one of these options, you are not prompted for format information. The formatting is taken care of automatically.

Now let’s look at an example that demonstrates how the format options work. The following bcp command includes the native format option (-n):

bcp AdventureWorks2008.HumanResources.Employee out C:\Data\EmployeeData_n.dat -n -S localhost\SqlSrv2008 -T

When you specify the -n argument, the data is automatically copied to the file in its native format. In the next example, the data is also automatically saved to the file without prompting for formatting information:

bcp AdventureWorks2008.HumanResources.Employee out C:\Data\EmployeeData_c.dat -c -S localhost\SqlSrv2008 -T

Notice that the -c argument is specified, rather than -n, as in the preceding example. As a result, the Employee information will now be saved as character data.

When the character format (-c) is used in a bcp command, each field, by default, is terminated with a tab character, and each row is terminated with a newline character. You can override the default behavior by using the -t argument to specify a field terminator and the -r argument to specify the row terminator. For example, the following bcp command species that each field be terminated with a comma:

bcp AdventureWorks2008.HumanResources.Employee out C:\Data\EmployeeData_c.dat -c -t, -S localhost\SqlSrv2008 -T

As you can see, the command includes the -t argument following by a comma, so each field in the data file will be terminated with a comma, rather than a tab. For a complete list of the types of terminators you can use and how to specify them, see the topic “Specifying Field and Row Terminators” in SQL Server Books Online.

Up to this point, the examples I’ve shown you have copied data from a table into a file. However, you can just as easily copy data from a view, as in the following example:

bcp AdventureWorks2008.HumanResources.vEmployee out C:\Data\EmployeeData_c.dat -c -t, -S localhost\SqlSrv2008 -T

This command is identical to the preceding example, except that it now extracts data from the vEmployee view, rather than the Employee table.

When your bcp command retrieves data from a table or view, it copies all the data. However, you have some control over which rows are copied to the data file. In a bcp command, you can use the -F argument to specify the first row to be retrieved and the -L argument to specify the last row. In the following example, the first row I retrieve is 101 and the last row is 200:

bcp AdventureWorks2008.HumanResources.Employee out C:\Data\EmployeeData_c.dat -c -S localhost\SqlSrv2008 -T -F 101 -L 200

Now the data file will include only the 100 rows that fall within the specified range.

The bcp utility also supports arguments that are not specific to the data itself. For example, you can use the -o argument to specify an output file. An output file captures the information normally returned to the command prompt after your run a bcp command. In the following example, I use the -o argument to specify that the output be saved to the EmployeeOutput.txt file:

bcp AdventureWorks2008.HumanResources.Employee out C:\Data\EmployeeData_c.dat -c -S localhost\SqlSrv2008 -T -o C:\Data\EmployeeOutput.txt

When you run this command, any output that would have been displayed to the console is now saved to the output file.

Exporting Data Returned by a Query

All the examples up to this point have used the out argument to copy data from a table or view. Now let’s look at the queryout argument, which retrieves data through a query. In the following example, I specify a SELECT statement, enclosed in quotation marks, and then specify the queryout argument:

bcp "SELECT * FROM AdventureWorks2008.Person.Person" queryout C:\Data\PersonData_n.dat -N -S localhost\SqlSrv2008 -T -L 100

As you can see, the command retrieves data from the Person table and uses the Unicode native format (-N) to save the data to the file. Notice that the command also includes the -L argument, which means that only 100 rows will be retrieved from the table. However, when you use the queryout option rather than the out option, you can be as specific in your query as necessary—you can include multiple tables and you can qualify your queries as necessary. For example, in the following statement, I include the TOP 100 clause in the SELECT statement, rather than include the -L argument:

bcp "SELECT TOP 100 * FROM AdventureWorks2008.Person.Person ORDER BY BusinessEntityID" queryout C:\Data\PersonData_c.dat -c -t, -S localhost\SqlSrv2008 -T

Notice that the query also includes an ORDER BY clause so the data in the file will be ordered by BusinessEntityID. This, of course, is still a very simple query, but it does demonstrate how to use the queryout argument in a bcp command and how similar this mode is to a command that contains the out argument. You still specify the data file, the format, and any other applicable options. Now let’s look at how to import data.

Importing Data into a Table

When you use the bcp utility to import data into a SQL Server table, you must specify the in mode, rather than out or queryout. To demonstrate how to use the in argument in a bcp command, I used the following Transact-SQL to create the Employees table:

USE AdventureWorks2008;
IF OBJECT_ID ('Employees', 'U') IS NOT NULL
DROP TABLE dbo.Employees;

CREATE TABLE dbo.Employees
(
BusinessEntityID int NOT NULL IDENTITY PRIMARY KEY,
NationalIDNumber nvarchar(15) NOT NULL,
LoginID nvarchar(256) NOT NULL,
OrganizationNode hierarchyid NOT NULL,
OrganizationLevel smallint NULL,
JobTitle nvarchar(50) NOT NULL,br> BirthDate date NOT NULL,
MaritalStatus nchar(1) NOT NULL,
Gender nchar(1) NOT NULL,
HireDate date NOT NULL,
SalariedFlag Flag NOT NULL,
VacationHours smallint NOT NULL,
SickLeaveHours smallint NOT NULL,
CurrentFlag Flag NOT NULL,br> rowguid uniqueidentifier NOT NULL,
ModifiedDate datetime NOT NULL
);

I created the table in the AdventureWorks2008 sample database in a SQL Server 2008 instance. After I created the table, I ran the following bcp command to create a data file that contains employee test data:

bcp AdventureWorks2008.HumanResources.Employee out C:\Data\EmployeeData_c.dat -c -t, -S localhost\SqlSrv2008 -T

We can now use this data file to demonstrate how to import data into the Employees table.

When you import data into a table, you must specify the table (or updatable view) and the in argument, as shown in the following example:

bcp AdventureWorks2008.dbo.Employees in C:\Data\EmployeeData_c.dat -c -t, -S localhost\SqlSrv2008 -T

Notice that you must also specify the source data file (C:\Data\EmployeeData_c.dat) and the options that define the format of the data as it was saved to the file: character format (-c) and field terminator (-t,). If you do not specify the correct format options, you will receive an error when you try to import the data.

You might have noticed that the BusinessEntityID column in the Employees table is configured as an IDENTITY column. As a result, when you import the data, the database engine, by default, ignores the BusinessEntityID values that are in the data file and generates its own IDs. However, you can override the default behavior by specifying the -E argument, as shown in the following example:

bcp AdventureWorks2008..Employees in C:\Data\EmployeeData_c.dat -c -t, -S localhost\SqlSrv2008 -T -E

Now when you import the data, the BusinessEntityID values in the data file will be loaded into the table, rather than new values being generated.

You can also order the data that you import into the table by using the -h argument along with the ORDER hint:

bcp AdventureWorks2008..Employees in C:\Data\EmployeeData_c.dat -c -t, -S localhost\SqlSrv2008 -T -E -h ORDER(BusinessEntityID)

After I specify -h ORDER, I provide the name of the column (in parentheses) whose values should be sorted. If you want to sort multiple columns, you must separate the columns with a comma.

The -h argument supports multiple hints. Another one, for example, is the TABLOCK hint, which specifies that a bulk update table-level lock should be acquired during the bulk operation. The following bcp command uses the TABLOCK hint:

bcp AdventureWorks2008..Employees in C:\Data\EmployeeData_c.dat -c -t, -S localhost\SqlSrv2008 -T -h TABLOCK

Because the command includes the TABLOCK hint, the database engine will hold the lock for the duration of the bulk load operation, which significantly improves performance over the default row-level locks.

When you run a bcp command, you can also specify the number of rows per batch of imported data. To specify the batch size, include the -b argument, along with the number of rows per batch. For example, the following bcp command limits each batch to 100 rows:

bcp AdventureWorks2008..Employees in C:\Data\EmployeeData_c.dat -c -t, -S localhost\SqlSrv2008 -T -b 100

The bcp utility also lets you specify an error file that stores any rows that the utility cannot copy from a data file into a table. To specify an error file, use the -e argument, followed by the full path name of the file, as shown in the following example:

bcp AdventureWorks2008..Employees in C:\Data\EmployeeData_c.dat -c -t, -S localhost\SqlSrv2008 -T -e c:\Data\EmployeeErrors.txt

When you run this command, any rows that cannot be imported into the Employees table will be saved to the EmployeeErrors.txt file.

Using Format Files to Import and Export Data

The bcp utility lets you create format files that store format information that can be used to import and export data. Each format file contains formatting information about the data in the data file as well as information about the target database table. Essentially, the format file maps the fields in the data file to the columns in the table. By using format files, you have far more flexibility in the type of data files you can use, allowing you to work with data files that can be shared by other applications or used for different SQL Server tables.

You can use the bcp utility to create a format file. Once you’ve created the file, you can then modify it as necessary to support bulk copy operations. To create a format file, you must specify format for the mode and nul for the data file. In addition, you must also include the -f argument, followed by the full path name of the format file, as shown in the following example:

bcp AdventureWorks2008.Person.Person format nul -c -t, -f C:\Data\PersonFormat_c.fmt -S localhost\sqlsrv2008 -T

As you can see, the format file is based on the Person table. In addition, the data will be saved to the data file with the character format and a comma as the field terminator. The format file produced by this command will look similar to the following:

10.0
13
1   SQLCHAR     0     12    ","     1   BusinessEntityID       ""
2   SQLCHAR     0     4     ","     2   PersonType             SQL_Latin1_General_CP1_CI_AS
3   SQLCHAR     0     3     ","     3   NameStyle              ""
4   SQLCHAR     0     16    ","     4   Title                  SQL_Latin1_General_CP1_CI_AS
5   SQLCHAR     0     100   ","     5   FirstName              SQL_Latin1_General_CP1_CI_AS
6   SQLCHAR     0     100   ","     6   MiddleName             SQL_Latin1_General_CP1_CI_AS
7   SQLCHAR     0     100   ","     7   LastName               SQL_Latin1_General_CP1_CI_AS
8   SQLCHAR     0     20    ","     8   Suffix                 SQL_Latin1_General_CP1_CI_AS
9   SQLCHAR     0     12    ","     9   EmailPromotion         ""
10  SQLCHAR     0     0     ","     10  AdditionalContactInfo  ""
11  SQLCHAR     0     0     ","     11  Demographics           ""
12  SQLCHAR     0     37    ","     12  rowguid                ""
13  SQLCHAR     0     24    "\r\n"  13  ModifiedDate           ""

The file includes such details as the data types of the data in the data file (second column), the field and row terminators (fifth column), and a mapping between table columns and the fields in the data file (sixth column). Note, however, that a full explanation of format files is beyond the scope of this article, and you should refer to the topic “Format Files for Importing or Exporting Data” in SQL Server Books Online for a complete explanation of how the files work. The main focus of this section is to show you how to use the bcp utility to create format files and use them to export and import data.

Returning to the format file above, notice that it lists SQLCHAR as the data type for all fields in the data file. This is because the character format (-c) is used to create the format file. However, you can also use one of the other format options to create the format file. For example, the following bcp command includes the native format (-n) argument, rather than the character format:

bcp AdventureWorks2008.Person.Person format nul -n -f C:\Data\PersonFormat_n.fmt -S localhost\sqlsrv2008 -T

Now the data types for the data file will show the native data types, as shown in the following:

10.0
13
1   SQLINT      0     4     ""      1   BusinessEntityID       ""
2   SQLNCHAR    2     4     ""      2   PersonType             SQL_Latin1_General_CP1_CI_AS
3   SQLBIT      1     1     ""      3   NameStyle              ""
4   SQLNCHAR    2     16    ""      4   Title                  SQL_Latin1_General_CP1_CI_AS
5   SQLNCHAR    2     100   ""      5   FirstName              SQL_Latin1_General_CP1_CI_AS
6   SQLNCHAR    2     100   ""      6   MiddleName             SQL_Latin1_General_CP1_CI_AS
7   SQLNCHAR    2     100   ""      7   LastName               SQL_Latin1_General_CP1_CI_AS
8   SQLNCHAR    2     20    ""      8   Suffix                 SQL_Latin1_General_CP1_CI_AS
9   SQLINT      0     4     ""      9   EmailPromotion         ""
10  SQLNCHAR    8     0     ""      10  AdditionalContactInfo  ""
11  SQLNCHAR    8     0     ""      11  Demographics           ""
12  SQLUNIQUEID 1     16    ""      12  rowguid                ""
13  SQLDATETIME 0     8     ""      13  ModifiedDate           ""

As you can see, because the native format is specified, the field and row terminators are no longer required, but the prefix length (the third column) is now specified. The prefix length indicates how much space the field requires. Again, refer to SQL Server Books Online for a complete description of format files.

The file created above is only one of two types of format files supported by SQL Server 2005 and 2008. The format file above is a non-XML format file, the original form of the file supported by earlier versions of SQL Server (and still supported in SQL Server 2005 and 2008). However, a newer type of format file—the XML format file—was introduced in SQL Server 2005. The XML format file is more flexible and powerful than the original non-XML format file.

To create an XML format file, you run a command similar to the preceding two examples; however, you must also include the -x argument, as shown in the following example:

bcp AdventureWorks2008.Person.Person format nul -c -t, -f C:\Data\PersonFormat_c.xml -S localhost\sqlsrv2008 -x -T

Now the data formatting information is stored as XML:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="3"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="16" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
  <FIELD ID="10" xsi:type="CharTerm" TERMINATOR=","/>
  <FIELD ID="11" xsi:type="CharTerm" TERMINATOR=","/>
  <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="37"/>
  <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="BusinessEntityID" xsi:type="SQLINT"/>
  <COLUMN SOURCE="2" NAME="PersonType" xsi:type="SQLNCHAR"/>
  <COLUMN SOURCE="3" NAME="NameStyle" xsi:type="SQLBIT"/>
  <COLUMN SOURCE="4" NAME="Title" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="5" NAME="FirstName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="6" NAME="MiddleName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="7" NAME="LastName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="8" NAME="Suffix" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="9" NAME="EmailPromotion" xsi:type="SQLINT"/>
  <COLUMN SOURCE="10" NAME="AdditionalContactInfo" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="11" NAME="Demographics" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="12" NAME="rowguid" xsi:type="SQLUNIQUEID"/>
  <COLUMN SOURCE="13" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
 </ROW>
</BCPFORMAT>

In an XML format file, the <RECORD> element lists the fields in the data file, and the <ROW> element lists the columns in the table. Notice that the xsi:type element in each field in the <RECORD> element lists the type as CharTerm, which is used for each field when the character format (-c) is specified. However, as with the non-XML format file, you can specify a different format, as shown in the following example:

bcp AdventureWorks2008.Person.Person format nul -n -f C:\Data\PersonFormat_n.xml -S localhost\sqlsrv2008 -x -T

Now the format file will include the native types and a prefix length, rather than a terminator:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="NativeFixed" LENGTH="4"/>
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
  <FIELD ID="4" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="16" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="6" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="7" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="8" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="9" xsi:type="NativeFixed" LENGTH="4"/>
  <FIELD ID="10" xsi:type="NCharPrefix" PREFIX_LENGTH="8"/>
  <FIELD ID="11" xsi:type="NCharPrefix" PREFIX_LENGTH="8"/>
  <FIELD ID="12" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
  <FIELD ID="13" xsi:type="NativeFixed" LENGTH="8"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="BusinessEntityID" xsi:type="SQLINT"/>
  <COLUMN SOURCE="2" NAME="PersonType" xsi:type="SQLNCHAR"/>
  <COLUMN SOURCE="3" NAME="NameStyle" xsi:type="SQLBIT"/>
  <COLUMN SOURCE="4" NAME="Title" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="5" NAME="FirstName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="6" NAME="MiddleName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="7" NAME="LastName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="8" NAME="Suffix" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="9" NAME="EmailPromotion" xsi:type="SQLINT"/>
  <COLUMN SOURCE="10" NAME="AdditionalContactInfo" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="11" NAME="Demographics" xsi:type="SQLNVARCHAR"/>
  <COLUMN   <COLUMN  </ROW>
</BCPFORMAT>

After you create the format file, you can then reference it in your bcp commands by specifying the -f argument, along with the name of the format file, as shown in the following example:

bcp AdventureWorks2008.Person.Person out C:\Data\PersonData_c.dat -f C:\Data\PersonFormat_c.fmt -S localhost\sqlsrv2008 -T -L 100

Notice that the command no longer needs a format option (such as -c) because all the formatting information is included in the format file. And you can specify any type of format file, as long as the formats of the specified fields are compatible with the columns in the table. In the following example, I use to the native format to export the data:

bcp AdventureWorks2008.Person.Person out C:\Data\PersonData_n.dat -f C:\Data\PersonFormat_n.fmt -S localhost\sqlsrv2008 -T -L 100

And you can just as easily specify the XML format file. The following example uses the character format XML file to export data:

bcp AdventureWorks2008.Person.Person out C:\Data\PersonData_c.dat -f C:\Data\PersonFormat_c.xml -S localhost\sqlsrv2008 -T -L 100

And the next example uses the native format XML file to export data:

bcp AdventureWorks2008.Person.Person out C:\Data\PersonData_n.dat -f C:\Data\PersonFormat_n.xml -S localhost\sqlsrv2008 -T -L 100

Of course, you can also use format files to import data. To demonstrate how to use the format files, I used the following Transact-SQL to create the Contacts1 table in the AdventureWorks2008 database:

USE AdventureWorks2008;

IF OBJECT_ID ('Contacts1', 'U') IS NOT NULL
DROP TABLE dbo.Contacts1;
 
SELECT *
INTO dbo.Contacts1
FROM AdventureWorks2008.Person.Person
WHERE 1 = 2;

Because I created the Contacts1 table based on the Person table, I can use one of the format files created above to import data into the Contact1 table. In the following example, I use the character format XML file to import data from the PersonData_c.dat file:

bcp AdventureWorks2008..Contacts1 in C:\Data\PersonData_c.dat -f C:\Data\PersonFormat_c.xml -S localhost\sqlsrv2008 -T

As you can see, I simply use the -f argument to specify the name of the format file. And I can do the same thing for the native format XML file:

bcp AdventureWorks2008..Contacts1 in C:\Data\PersonData_n.dat -f C:\Data\PersonFormat_n.xml -S localhost\sqlsrv2008 -T

In the examples we’ve looked at so far, the columns in the SQL Server tables have matched the fields in the data files. However, sometimes there are more columns in the data file than in the table, more columns in the table than in the data file, or the columns are in a different order between the two sources. Under such scenarios, format files are ideal for mapping the columns and fields to each other, so let’s look at a few examples that demonstrate how this is done.

More Columns in Data File than in Table

When importing data from a file that contains more fields than there are columns in the target table, you can configure the format file to accommodate these differences. First, however, to demonstrate how to import the data, I used the following Transact-SQL to create the Contacts2 table:

USE AdventureWorks2008;
 
IF OBJECT_ID ('Contacts2', 'U') IS NOT NULL
DROP TABLE dbo.Contacts2;
 
SELECT BusinessEntityID AS ContactID,
FirstName,
LastName,
Demographics,
rowguid,
ModifiedDate
INTO dbo.Contacts2
FROM AdventureWorks2008.Person.Person
WHERE 1 = 2;

The new table is based on the Person table, but doesn’t use all of the columns. As a result, the data file generated from the Person table will contain more fields than the Contacts2 table. To address this issue, I modified the character data XML file (PersonFormat_c.xml) so that only the columns in the Contacts2 table are included, as shown in the following file contents:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="3"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="16" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
  <FIELD ID="10" xsi:type="CharTerm" TERMINATOR=","/>
  <FIELD ID="11" xsi:type="CharTerm" TERMINATOR=","/>
  <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="37"/>
  <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="BusinessEntityID" xsi:type="SQLINT"/>
  <COLUMN SOURCE="5" NAME="FirstName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="7" NAME="LastName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="11" NAME="Demographics" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="12" NAME="rowguid" xsi:type="SQLUNIQUEID"/>
  <COLUMN SOURCE="13" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
 </ROW>
</BCPFORMAT>

Notice that the columns listed in the <ROW> element now match the Contacts2 table. To match the columns to the fields in the data file, you must make sure that the SOURCE element value matches the appropriate ID value in the FIELD elements. For example, the BusinessEntityID column shows a SOURCE value of 1. This matches the first field listed in the <RECORD> element, which is ID 1.

After I modified the PersonFormat_c.xml file, I renamed it PersonFormat_c2.xml. I can then use the file in my bcp command to import data into the Contacts2 table, as shown in the following example:

bcp AdventureWorks2008..Contacts2 in C:\Data\PersonData_c.dat -f C:\Data\PersonFormat_c2.xml -S localhost\sqlsrv2008 -T

Because the format file has been configured to include only the Contacts2 column and those columns map the appropriate fields, only the correct data from the data file is imported into the table.

More Columns in Table than in Data File

In some cases, your target table will include more values than are in the data file, as is sometimes the case when you have nullable columns or columns with default or computed values. In such cases, you’ll need to create a format file that reflects the difference between the source and target.

To demonstrate how this works, I used the following Transact-SQL to create the Contacts3 table and then to define a default value on the Suffix column:

USE AdventureWorks2008;
 
IF OBJECT_ID ('Contacts3', 'U') IS NOT NULL
DROP TABLE dbo.Contacts3;
 
SELECT BusinessEntityID AS ContactID,
FirstName,
LastName,
Suffix,
Demographics,
rowguid,
ModifiedDate
INTO dbo.Contacts3
FROM AdventureWorks2008.Person.Person
WHERE 1 = 2;
 
ALTER TABLE Contacts3
ADD CONSTRAINT suffix_def DEFAULT 'unknown' FOR Suffix;

After I created the table, I exported the data from Contacts2, the table I populated in the previous example:

bcp AdventureWorks2008..Contacts2 out C:\Data\PersonData_c2.dat -c -t, -S localhost\sqlsrv2008 -T

The result is that the Contacts3 table contains a Suffix column (with a default), but the data file does not contain a matching field. Next, I create a format file based on the Contacts3 table:

bcp AdventureWorks2008..Contacts3 format nul -c -t, -f C:\Data\PersonFormat_c3.fmt -S localhost\sqlsrv2008 -T

Notice that I created a non-XML format file. This is because the XML format files do not let you have more table columns than fields in the data file. Every column listed in the <ROW> element of the XML file must correspond to a field in the <RECORD> element. (You can get around this limitation by creating an updateable view against the target table and including only those columns that have corresponding fields in the data file.)

After you create the format file, you can modify the file to map the columns to the data fields. In this case, I deleted the Suffix row and updated the numbers in the first column (the numbers that represent the data file field order) so they’re in consecutive order. The format file should now look similar to the following:

10.0
6
1   SQLCHAR     0     12    ","     1   ContactID          ""
2   SQLCHAR     0     100   ","     2   FirstName          SQL_Latin1_General_CP1_CI_AS
3   SQLCHAR     0     100   ","     3   LastName           SQL_Latin1_General_CP1_CI_AS
4   SQLCHAR     0     0     ","     5   Demographics       ""
5   SQLCHAR     0     37    ","     6   rowguid            ""
6   SQLCHAR     0     24    "\r\n"  7   ModifiedDate       ""

Once you’ve modified the format file, you can use the following bcp command to import the data:

bcp AdventureWorks2008..Contacts3 in C:\Data\PersonData_c2.dat -f C:\Data\PersonFormat_c3.fmt -S localhost\sqlsrv2008 -T

Notice that I’m using the PersonData_c2.data data file and the PersonFormat_c3.fmt format file. When you run this command, the database engine will automatically insert the default value into the Suffix column.

Columns in Data File in Different Order than Table

In some cases, the columns in a table will be in a different order from the fields in a data file. You can modify either the non-XML or XML format files to accommodate these differences. In either case, you should make certain that the columns map to the correct fields in the format files. To demonstrate how to map the columns, I used the following Transact-SQL to create the Contacts4 table:

USE AdventureWorks2008;
 
IF OBJECT_ID ('Contacts4', 'U') IS NOT NULL
DROP TABLE dbo.Contacts4;
 
SELECT BusinessEntityID AS ContactID,
  LastName,
  FirstName,
  Demographics,
  rowguid,
  ModifiedDate
INTO dbo.Contacts4
FROM AdventureWorks2008.Person.Person
WHERE 1 = 2;

In this case, I switched the FirstName and LastName fields from the source table (Person). Now let’s look at the non-XML format file. For this example, I created a format file and data file based on the Person table, as we saw in earlier examples. I then modified the format file to support the Contacts4 table, as shown in the following:

10.0
13
1   SQLCHAR     0     12    ","     1   BusinessEntityID       ""
2   SQLCHAR     0     4     ","     0   PersonType             SQL_Latin1_General_CP1_CI_AS
3   SQLCHAR     0     3     ","     0   NameStyle              ""
4   SQLCHAR     0     16    ","     0   Title                  SQL_Latin1_General_CP1_CI_AS
5   SQLCHAR     0     100   ","     3   FirstName              SQL_Latin1_General_CP1_CI_AS
6   SQLCHAR     0     100   ","     0   MiddleName             SQL_Latin1_General_CP1_CI_AS
7   SQLCHAR     0     100   ","     2   LastName               SQL_Latin1_General_CP1_CI_AS<
8   SQLCHAR     0     20    ","     0   Suffix                 SQL_Latin1_General_CP1_CI_AS
9   SQLCHAR     0     12    ","     0   EmailPromotion         ""
10  SQLCHAR     0     0     ","     0   AdditionalContactInfo  ""
11  SQLCHAR     0     0     ","     4   Demographics           ""
12  SQLCHAR     0     37    ","     5   rowguid                ""
13  SQLCHAR     0     24    "\r\n"  6   ModifiedDate           ""

The first modification I made is to change the numbering in the sixth column of the format file (the numbering for the server column order). Any row that includes a field in the data file that is not a column in the table should be set to 0. In addition, the number should be modified to reflect the order of the columns in the table. For example, the FirstName column is now set to 3 because it is the third column in the table, and the LastName column is set to 2 because it is the second column in the table.

After I made these changes, I saved the format file as PersonFormat_c4.fmt. You can now use the format file to load data into the Contacts4 table, as shown in the following bcp command:

bcp AdventureWorks2008..Contacts4 in C:\Data\PersonData_c.dat -f C:\Data\PersonFormat_c4.fmt -S localhost\sqlsrv2008 -T

You can achieve the same results by creating an XML format file. The key is to include only those columns in the <ROW> element that are also contained in the Contacts4 table and to list those columns in the same order as they appear in the table, as shown in the following:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="3"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="16" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
  <FIELD ID="10" xsi:type="CharTerm" TERMINATOR=","/>
  <FIELD ID="11" xsi:type="CharTerm" TERMINATOR=","/>
  <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="37"/>
  <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="BusinessEntityID" xsi:type="SQLINT"/>
  <COLUMN SOURCE="7" NAME="LastName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="5" NAME="FirstName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="11" NAME="Demographics" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="12" NAME="rowguid" xsi:type="SQLUNIQUEID"/>
  <COLUMN SOURCE="13" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
 </ROW>
</BCPFORMAT>

To create this file, I generated an XML format file based on the Person table, modified the file, and saved it as PersonFormat_c4.xml. Note that, after I eliminated the extra columns from the <ROW> element and ensured that they were in the correct order, I modified the SOURCE attribute as necessary to map the columns to the fields. For example, the LastName column has a SOURCE value of 7 to match the field with an ID value of 7.

After you’ve created the format file, you can run a bcp command similar to the following to import the data into the Contact4 table:

bcp AdventureWorks2008..Contacts4 in C:\Data\PersonData_c.dat -f C:\Data\PersonFormat_c4.xml -S localhost\sqlsrv2008 -T

When you run this command, the data will be imported from the PersonData_c.dat file into the Contacts4 table, and the import operation will be based on the PersonFormat_c4.xml format file.

Using the bcp Utility

As you’ve seen from the examples in this article, the bcp command-line utility provides a great deal of flexibility when importing and exporting data. You can copy data in its native format in order to transfer data between instances of SQL Server, or you can copy it as character data in order to work with applications other than SQL Server. And the bcp utility supports numerous options that let you fine-tune your import and export operations. In addition, you can use the utility to generate format files and then use those files for your bulk copy operations. Indeed, once you’ve learned to work with the bcp utility, you should be able to handle most of your bulk copy needs.

Robert Sheldon

Author profile:

After being dropped 35 feet from a helicopter and spending the next year recovering, Robert Sheldon left the Colorado Rockies and emergency rescue work to pursue safer and less painful interests—thus his entry into the world of technology. He is now a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. He has also written news stories, feature articles, restaurant reviews, legal summaries, and the novel 'Dancing the River Lightly'. You can find more information at http://www.rhsheldon.com.

Search for other articles by Robert Sheldon

Rate this article:   Avg rating: from a total of 116 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: BCP Utility
Posted by: Paul Hunter (view profile)
Posted on: Thursday, December 10, 2009 at 8:45 PM
Message: Robert,
This is a very nice article and is to the point on loading files using BCP. I created a stored procedure that will help create the non-xml format file. The script can be found at:
http://www.sqlservercentral.com/scripts/SQL+Server+2005+%2f+SQL+Server+2008/67764/

I'd appreciate any feedback on the procedure.

Thanks

Subject: Thanks a lot and a query!!
Posted by: kartik joshi (not signed in)
Posted on: Friday, December 11, 2009 at 4:16 AM
Message: Very well written and informtive article.

I am trying to use the bcp command and the problem faced by me is to connect to remote server.Iam getting a timeout message.My questions are as follows.

1.Does bcp for remote server works only when u have a SQL server installed in your local system?

2.What are the configuration of remote server so that bcp can access and work accordingly.

My questions might seems silly to you,but i would be grateful if you can address my concerns.


Thanks in anticipation

Subject: BCP Support
Posted by: Paul Hunter (view profile)
Posted on: Sunday, December 13, 2009 at 10:22 PM
Message: Kartik,
Remember the form of the command:

bcp "destination/source table/view/procedure/SQL" direction "source/destination file" arguments

The way I normally handle it is to have it run from the server but I assume you could run it from anywhere as long as you have a "fully qualified" name to the object ([server].[db].[schema].[object]) and rights to access the file.

Subject: Re:BCP Support
Posted by: Anonymous (not signed in)
Posted on: Sunday, December 13, 2009 at 10:59 PM
Message: Hi Paul,

Thanks for the support,the information was helpful and now I am able to connect to the remote server and peroform Import and Export operations.
However, the same actions cannot be performed from another system that hasn't got SQL server Installed in it..even though iam able to connect to remote server from that system.
The error that Iam getting when i run the bat file containing bcp is

"bcp is not recognised as an internal or external command"

is this because there is no SQL installed in it?

please suggest a way to fix this issue.
Thanks for your continued support.

Subject: Is bcp quicker than SSIS?
Posted by: Niall (not signed in)
Posted on: Sunday, December 13, 2009 at 11:03 PM
Message: Just wondering whether bcp is quicker for a data load than SSIS?

Subject: Thanks and a query
Posted by: Kartik Joshi (not signed in)
Posted on: Monday, December 14, 2009 at 2:28 AM
Message: Hi Paul/Robert,

Thanks for the support,the information was helpful and now I am able to connect to the remote server and peroform Import and Export operations.
However, the same actions cannot be performed from another system that hasn't got SQL server Installed in it..even though iam able to connect to remote server from that system.
The error that Iam getting when i run the bat file containing bcp is

"bcp is not recognised as an internal or external command"

is this because there is no SQL installed in it?

please suggest a way to fix this issue.
Thanks for your continued support.

Subject: Thanks and a query
Posted by: Kartik Joshi (not signed in)
Posted on: Monday, December 14, 2009 at 3:22 AM
Message: Hi Paul/Robert,
seems this question is copied many times,apologise for it. :)
Thanks for the support,the information was helpful and now I am able to connect to the remote server and peroform Import and Export operations.
However, the same actions cannot be performed from another system that hasn't got SQL server Installed in it..even though iam able to connect to remote server from that system.
The error that Iam getting when i run the bat file containing bcp is

"bcp is not recognised as an internal or external command"

is this because there is no SQL installed in it?

please suggest a way to fix this issue.
Thanks for your continued support.

Subject: Columns in Data File in Different Order than Table
Posted by: João Angelo (not signed in)
Posted on: Monday, December 14, 2009 at 5:32 AM
Message: Hi,

first of all, thank you for this in depth article.

A while ago I was faced with some situations described in this article like export/import to tables with columns in different order.

At that time I wrote a small utility to handle the automatic update of the format file. More details at my blog (exceptionalcode.wordpress.com) under the Database category.

Subject: BCP fails on spaces or dots in database name
Posted by: Anonymous (not signed in)
Posted on: Monday, December 14, 2009 at 10:51 AM
Message: Hi,

Thank you for this artical. I am a long time fan of the BCP commandline utility. There is however one problem I haven't been able to tackle. When the name of the database contains a space or a dot then I can't get BCP to run. I've tried using double quotes ("") and square brackets ([]) and combinations of both.

Thanx in advance!

Subject: More Support
Posted by: Paul Hunter (view profile)
Posted on: Monday, December 14, 2009 at 2:07 PM
Message: @Niall -- SSIS can handle master/detail, data cleaning, validation, exceptions and a multitude of conditions. BCP is fast but dumb. If you're loading data in a standardized format that's all at the same grain (or if you don't care about the granularity) then BCP will always out perform SSIS.

@Anonymous - as a practice, I always enclose the SQL/object in double quoted "[DBname].[schema].[object]" (I do the same for the file) this way I don't care if there are embedded spaces. I've never had this fail. If you're still having issues please post a specific example.

Subject: CSV FILE CONTAINING DIFFERENT ORDER
Posted by: John Cusak (not signed in)
Posted on: Friday, December 18, 2009 at 6:59 AM
Message: Hi

iam tryin to load a csv file in existing Database table,which already has some data..the csv file has a header and data in jumbled order as that of the header in DB table. I tried the approach mentioned my you...but it isnt workin for me.

bcp joshi.dbo.kartik in D:\employee_out.csv -F2 -f D:\PersonFormat_c.fmt -Srtih\sqlexpress

-Uabc -Pxyz

kindly suggest a way if there is fault in the above code and also the format file generated is also using the code mentioned by you.

Subject: Important options not to forget
Posted by: ClementHuge (view profile)
Posted on: Wednesday, September 08, 2010 at 2:36 AM
Message: Very interested article with real life experience issues.
To add into the experience issues, there are three additional points to care about.

1. BCP security: Obviously if you plan to use it within a sql server job for scheduled import and not use SSIS, you can use proxy account.

2. BCP vs referential integrity: I have been exposed to an issue concrning a table with constraint (FK, CK, PK). It is advisable to use the option -E for the BCP in.

3. BCP blocksize: Although the default block size is 1000 rows, Microsoft has set up the behavious to be a one large transaction for the entire BCP. You should expect tiny transaction 1k-row per time but it is not! I suffered them during a development when the text file contained 50 million rows and maxed out the log in my database. So I would advise to always set up a block size (-b) so that you are aware of the real block that is inserted within the same transaction. I noticed a dramatic performance boost and the log was contained completely.

Subject: BCP usability
Posted by: jayemil (view profile)
Posted on: Wednesday, August 24, 2011 at 8:46 AM
Message: As a new SQL developer. This is a great.... Thanks guys

Subject: Sybase/Unix
Posted by: Cynthia (view profile)
Posted on: Thursday, May 31, 2012 at 9:42 AM
Message: I'm attempting to use BCP to read a Sybase ASE table, on a *nix server, and write the contents of the table into a file. However, when I use the syntax such as this example above:

bcp "SELECT * FROM AdventureWorks2008.Person.Person" queryout C:\Data\PersonData_n.dat -N -S localhost\SqlSrv2008 -T -L 100

I get an error that says:
Syntax Error in 'QUERYOUT'.

Googling about this, I found one comment that says "You cannot have SARG in bcp. BCP does not understand T-SQL."

Is this only true for a *nix/Sybase type system? As the example above is said to work on Windows. I don't have any Windows servers to try it on so I don't know.

I may have to create a view, I guess, but since my scripting experience is extremely limited, it takes me a while to figure it out. I was hoping the one-liner BCP to read the DB and write to the file would just work. Thanks for the article.


Subject: bcp Format File
Posted by: gsuarez (view profile)
Posted on: Monday, February 18, 2013 at 11:40 AM
Message: Nice Article, when I use the syntax such as this example above:
EXEC xp_cmdshell 'bcp "SELECT * FROM ##MSISCONT ORDER BY Number ASC" queryout "H:\Area\MSISCONT.txt" -T -t -w'

Then I tried to import from third party software and gives me an error but if i copy the content and paste it into new txt file created manually (non bcp) the import process success.

Subject: bcp Format File
Posted by: gsuarez (view profile)
Posted on: Monday, February 18, 2013 at 2:23 PM
Message: Nice Article, when I use the syntax such as this example above:
EXEC xp_cmdshell 'bcp "SELECT * FROM ##MSISCONT ORDER BY Number ASC" queryout "H:\Area\MSISCONT.txt" -T -t -w'

Then I tried to import from third party software and gives me an error but if i copy the content and paste it into new txt file created manually (non bcp) the import process success.

Subject: bcp Format File
Posted by: gsuarez (view profile)
Posted on: Monday, February 18, 2013 at 4:48 PM
Message: Nice Article, when I use the syntax such as this example above:
EXEC xp_cmdshell 'bcp "SELECT * FROM ##MSISCONT ORDER BY Number ASC" queryout "H:\Area\MSISCONT.txt" -T -t -w'

Then I tried to import from third party software and gives me an error but if i copy the content and paste it into new txt file created manually (non bcp) the import process success.

Subject: bcp Format File
Posted by: gsuarez (view profile)
Posted on: Monday, February 18, 2013 at 4:56 PM
Message: Nice Article, when I use the syntax such as this example above:
EXEC xp_cmdshell 'bcp "SELECT * FROM ##MSISCONT ORDER BY Number ASC" queryout "H:\Area\MSISCONT.txt" -T -t -w'

Then I tried to import from third party software and gives me an error but if i copy the content and paste it into new txt file created manually (non bcp) the import process success.

Subject: facing problem while executing bcp command
Posted by: ashu.harshwardhan (view profile)
Posted on: Wednesday, August 28, 2013 at 6:11 AM
Message: Hi Robert,

I am using exec master..xp_cmdshell command to execute bcp. After executing stored procedure it's not showing any error and also not working as expected.
I am using bcp command to copy data from source file into table, but after execution no data is copied into table. I have already created table and have enabled xp_cmdshell command and have given sysadmin role to user.

Any suggestion?

Subject: exporting to a remote server
Posted by: Tiana0000 (view profile)
Posted on: Tuesday, November 19, 2013 at 9:23 AM
Message: Hi. Pls i'd like to use the bcp utility to export a query result to a remote server which i access using its IP address. I've tried...

bcp "SELECT * FROM table_name" queryout \\10.253.1.5\c$\share\export_data.dat" -c -t, -S 10.253.1.5 -T

and it didnt work. Pls help

Subject: exporting to a remote server
Posted by: Tiana0000 (view profile)
Posted on: Wednesday, November 20, 2013 at 3:16 AM
Message: Hi. Pls i'd like to use the bcp utility to export a query result to a remote server which i access using its IP address. I've tried...

bcp "SELECT * FROM table_name" queryout \\10.253.1.5\c$\share\export_data.dat" -c -t, -S 10.253.1.5 -T

and it didnt work. Pls help

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.