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 confirm
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 | null) is the full path
of the data file or, when a data file should not be specified, the null 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 null 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.
-a packet_size
Specifies the number of bytes, per network packet, sent to and from the server. A server configuration option can be set by using SQL Server Management Studio (or the sp_configure system stored procedure). However, the server configuration option can be overridden on an individual basis by using this option. packet_size can be from 4096 to 65535 bytes;
Specifies the number of bytes, per network packet, sent to and from the server. A server configuration option can be set by using SQL Server Management Studio (or the sp_configure system stored procedure). However, the server configuration option can be overridden on an individual basis by using this option. packet_size can be from 4096 to 65535 bytes;
The default is
4096.
Increased packet
size can enhance performance of bulk-copy operations. If a larger packet is
requested but cannot be granted, the default is used. The performance
statistics generated by the bcp utility show the packet size used.
-b batch_size
Specifies the number of rows per batch of imported data. Each batch is imported and logged as a separate transaction that imports the whole batch before being committed. By default, all the rows in the data file are imported as one batch. To distribute the rows among multiple batches, specify a batch_size that is smaller than the number of rows in the data file. If the transaction for any batch fails, only insertions from the current batch are rolled back. Batches already imported by committed transactions are unaffected by a later failure.
Specifies the number of rows per batch of imported data. Each batch is imported and logged as a separate transaction that imports the whole batch before being committed. By default, all the rows in the data file are imported as one batch. To distribute the rows among multiple batches, specify a batch_size that is smaller than the number of rows in the data file. If the transaction for any batch fails, only insertions from the current batch are rolled back. Batches already imported by committed transactions are unaffected by a later failure.
Do not use this
option in conjunction with the -h "ROWS_PER_BATCH =bb" option.
-S server_name [\instance_name]:
Specifies the
instance of SQL Server to which to connect. If no server is specified, the bcp
utility connects to the default instance of SQL Server on the local computer.
This option is required when a bcp command is run from a remote computer on the
network or a local named instance. To connect to the default instance of SQL
Server on a server.
Specify only
server_name. To connect to a named instance of SQL Server, specify
server_name\instance_name.
-T
Specifies that
the bcp utility connects to SQL Server with a trusted connection using
integrated security. The security credentials of the network user, login_id,
and password are not required. If –T is not specified, you need to specify –U
and –P to successfully log in.
Important
When the bcp
utility is connecting to SQL Server with a trusted connection using integrated
security, use the -T option (trusted connection) instead of the user name and
password combination. When the bcp utility is connecting to SQL Database or SQL
Data Warehouse, using Windows authentication or Azure Active Directory
authentication is not supported. Use the -U and -P options.
-U login_id
Specifies the
login ID used to connect to SQL Server.
Important
When the bcp
utility is connecting to SQL Server with a trusted connection using integrated
security, use the -T option (trusted connection) instead of the user name and
password combination. When the bcp utility is connecting to SQL Database or SQL
Data Warehouse, using Windows authentication or Azure Active Directory
authentication is not supported. Use the -U and -P options.
-P password
Specifies the
password for the login ID. If this option is not used, the bcp command prompts
for a password. If this option is used at the end of the command prompt without
a password, bcp uses the default password (NULL).
Important:
Do not use a
blank password. Use a strong password.
To mask your
password, do not specify the -P option along with the -U option. Instead, after
specifying bcp along with the -U option and other switches (do not specify -P),
press ENTER, and the command will prompt you for a password. This method
ensures that your password will be masked when it is entered.
If password
begins with a hyphen (-) or a forward slash (/), do not add a space between -P
and the password value.
bcp
AdventureWorks2008.HumanResources.Employee
out
C:\Data\EmployeeData.dat
-S
localhost\SqlSrv2008 –U acct1
bcp AdventureWorks2008.HumanResources.Employee
out
C:\Data\EmployeeData.dat
-S localhost\SqlSrv2008 -Uacct1
-Ppw123
-c
performs the operation using a character data type. This option does not prompt for each field; it uses char as the storage type, without prefixes and with \t (tab character) as the field separator and \r\n (newline character) as the row terminator. -c is not compatible with -w.
performs the operation using a character data type. This option does not prompt for each field; it uses char as the storage type, without prefixes and with \t (tab character) as the field separator and \r\n (newline character) as the row terminator. -c is not compatible with -w.
-n
Performs the
bulk-copy operation using the native (database) data types of the data. This
option does not prompt for each field; it uses the native values.
For more
information, see Use Native Format to Import or Export Data (SQL Server).
-N
Performs the
bulk-copy operation using the native (database) data types of the data for
noncharacter data, and Unicode characters for character data. This option
offers a higher performance alternative to the -w option, and is intended for
transferring data from one instance of SQL Server to another using a data file.
It does not prompt for each field. Use this option when you are transferring
data that contains ANSI extended characters and you want to take advantage of
the performance of native mode.
For more
information, see Use Unicode Native Format to Import or Export Data (SQL
Server).
If you export and
then import data to the same table schema by using bcp.exe with -N, you might
see a truncation warning if there is a fixed length, non-Unicode character
column (for example, char(10)).
The warning can
be ignored. One way to resolve this warning is to use -n instead of -N.
-w
Performs the bulk
copy operation using Unicode characters. This option does not prompt for each
field; it uses nchar as the storage type, no prefixes, \t (tab character) as
the field separator, and \n (newline character) as the row terminator. -w is
not compatible with -c.
For more
information, see Use Unicode Character Format to Import or Export Data (SQL
Server).
-d database_name
Specifies the
database to connect to. By default, bcp.exe connects to the user’s default
database. If -d database_name and a three part name
(database_name.schema.table, passed as the first parameter to bcp.exe) is
specified, an error will occur because you cannot specify the database name
twice.If database_name begins with a hyphen (-) or a forward slash (/), do not
add a space between -d and the database name.
-t field_term
Specifies the
field terminator. The default is \t (tab character). Use this parameter to
override the default field terminator. For more information, see Specify Field
and Row Terminators (SQL Server).
If you specify
the field terminator in hexadecimal notation in a bcp.exe command, the value
will be truncated at 0x00. For example, if you specify 0x410041, 0x41 will be
used.
If field_term
begins with a hyphen (-) or a forward slash (/), do not include a space between
-t and the field_term value.
-F first_row
Specifies the
number of the first row to export from a table or import from a data file. This
parameter requires a value greater than (>) 0 but less than (<) or equal
to (=) the total number rows. In the absence of this parameter, the default is
the first row of the file.
First_row can be
a positive integer with a value up to 2^63-1. -F first_row is 1-based.
-L last_row
Specifies the
number of the last row to export from a table or import from a data file. This
parameter requires a value greater than (>) 0 but less than (<) or equal
to (=) the number of the last row. In the absence of this parameter, the
default is the last row of the file.
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.
-o output_file
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.
If output_file
begins with a hyphen (-) or a forward slash (/), do not include a space between
-o and the output_file value.
-e err_file
Specifies the
full path of an error file used to store any rows that the bcp utility cannot
transfer from the file to the database. Error messages from the bcp command go
to the workstation of the user. If this option is not used, an error file is
not created.
If err_file
begins with a hyphen (-) or a forward slash (/), do not include a space between
-e and the err_file value.
ROWS_PER_BATCH = bb
Number of rows of
data per batch (as bb). Used when -b is not specified, resulting in the entire
data file being sent to the server as a single transaction. The server optimizes
the bulk load according to the value bb. By default, ROWS_PER_BATCH is unknown.
KILOBYTES_PER_BATCH = cc
Approximate
number of kilobytes of data per batch (as cc). By default, KILOBYTES_PER_BATCH
is unknown.
-q
Executes the SET
QUOTED_IDENTIFIERS ON statement in the connection between the bcp utility and
an instance of SQL Server. Use this option to specify a database, owner, table,
or view name that contains a space or a single quotation mark. Enclose the
entire three-part table or view name in quotation marks ("").
To specify a
database name that contains a space or single quotation mark, you must use the
–q option.
-q does not apply
to values passed to -d.
For more
information, see Remarks, later in this topic.
-E
Specifies that
identity value or values in the imported data file are to be used for the
identity column. If -E is not given, the identity values for this column in the
data file being imported are ignored, and SQL Server automatically assigns
unique values based on the seed and increment values specified during table
creation.
If the data file
does not contain values for the identity column in the table or view, use a
format file to specify that the identity column in the table or view should be
skipped when importing data; SQL Server automatically assigns unique values for
the column. For more information, see DBCC CHECKIDENT (Transact-SQL).
The -E option has
a special permissions requirement. For more information, see
"Remarks" later in this topic.
-f format_file
Specifies the
full path of a format file. The meaning of this option depends on the
environment in which it is used, as follows:
If -f is used
with the format option, the specified format_file is created for the specified table
or view. To create an XML format file, also specify the -x option. For more
information, see Create a Format File (SQL Server).
If used with the
in or out option, -f requires an existing format file.
Using a format
file in with the in or out option is optional. In the absence of the -f option,
if -n, -c, -w, or -N is not specified, the command prompts for format
information and lets you save your responses in a format file (whose default
file name is Bcp.fmt).
If format_file
begins with a hyphen (-) or a forward slash (/), do not include a space between
-f and the format_file value.
-h "load hints [ ,...
n]"
Specifies
the hint or hints to be used during a bulk import of data into a table or view.
ORDER(column[ASC | DESC] [,...n])
The sort order of
the data in the data file. Bulk import performance is improved if the data
being imported is sorted according to the clustered index on the table, if any.
If the data file is sorted in a different order, that is other than the order
of a clustered index key, or if there is no clustered index on the table, the
ORDER clause is ignored. The column names supplied must be valid column names
in the destination table. By default, bcp assumes the data file is unordered.
For optimized bulk import, SQL Server also validates that the imported data is
sorted.
TABLOCK
Specifies that a
bulk update table-level lock is acquired for the duration of the bulk load
operation; otherwise, a row-level lock is acquired. This hint significantly
improves performance because holding a lock for the duration of the bulk-copy
operation reduces lock contention on the table. A table can be loaded
concurrently by multiple clients if the table has no indexes and TABLOCK is
specified. By default, locking behavior is determined by the table option table
lock on bulk load.
If the target
table is clustered columnstore index, TABLOCK hint is not required for loading
by multiple concurrent clients because each concurrent thread is assigned a
separate rowgroup within the index and loads data into it. Please refer to
columnstore index conceptual topics for details,
CHECK_CONSTRAINTS
Specifies that
all constraints on the target table or view must be checked during the
bulk-import operation. Without the CHECK_CONSTRAINTS hint, any CHECK and
FOREIGN KEY constraints are ignored, and after the operation the constraint on
the table is marked as not-trusted.
Note
UNIQUE, PRIMARY
KEY, and NOT NULL constraints are always enforced.
At some point,
you will need to check the constraints on the entire table. If the table was
nonempty before the bulk import operation, the cost of revalidating the
constraint may exceed the cost of applying CHECK constraints to the incremental
data. Therefore, we recommend that normally you enable constraint checking
during an incremental bulk import.
A situation in which
you might want constraints disabled (the default behavior) is if the input data
contains rows that violate constraints. With CHECK constraints disabled, you
can import the data and then use Transact-SQL statements to remove data that is
not valid.
Note
bcp now enforces
data validation and data checks that might cause scripts to fail if they are
executed on invalid data in a data file.
Note
The -m max_errors
switch does not apply to constraint checking.
FIRE_TRIGGERS
Specified with
the in argument, any insert triggers defined on the destination table will run
during the bulk-copy operation. If FIRE_TRIGGERS is not specified, no insert
triggers will run. FIRE_TRIGGERS is ignored for the out, queryout, and format
arguments.
-i input_file
Specifies the
name of a response file, containing the responses to the command prompt
questions for each data field when a bulk copy is being performed using
interactive mode (-n, -c, -w, or -N not specified).
If input_file
begins with a hyphen (-) or a forward slash (/), do not include a space between
-i and the input_file value.
-k
Specifies that
empty columns should retain a null value during the operation, rather than have
any default values for the columns inserted. For more information, see Keep
Nulls or Use Default Values During Bulk Import (SQL Server).
-K application_intent
Declares the
application workload type when connecting to a server. The only value that is
possible is ReadOnly. If -K is not specified, the bcp utility will not support
connectivity to a secondary replica in an Always On availability group. For
more information, see Active Secondaries: Readable Secondary Replicas (Always
On Availability Groups).
-m max_errors
Specifies the
maximum number of syntax errors that can occur before the bcp operation is
canceled. A syntax error implies a data conversion error to the target data
type. The max_errors total excludes any errors that can be detected only at the
server, such as constraint violations.
A row that cannot
be copied by the bcp utility is ignored and is counted as one error. If this
option is not included, the default is 10.
Note
The -m option
also does not apply to converting the money or bigint data types.
-r row_term
Specifies the row
terminator. The default is \n (newline character). Use this parameter to
override the default row terminator. For more information, see Specify Field
and Row Terminators (SQL Server).
If you specify
the row terminator in hexadecimal notation in a bcp.exe command, the value will
be truncated at 0x00. For example, if you specify 0x410041, 0x41 will be used.
If row_term
begins with a hyphen (-) or a forward slash (/), do not include a space between
-r and the row_term value.
-R
Specifies that
currency, date, and time data is bulk copied into SQL Server using the regional
format defined for the locale setting of the client computer. By default,
regional settings are ignored.
-v
Reports the bcp
utility version number and copyright.
-V (80 | 90 | 100
| 110 | 120 | 130 )
Performs the
bulk-copy operation using data types from an earlier version of SQL Server.
This option does not prompt for each field; it uses the default values.
80 = SQL Server
2000
90 = SQL Server
2005
100 = SQL Server
2008 and SQL Server 2008 R2
110 = SQL Server
2012
120 = SQL Server
2014
130 = SQL Server
2016
For example, to
generate data for types not supported by SQL Server 2000, but were introduced
in later versions of SQL Server, use the -V80 option.
For more
information, see Import Native and Character Format Data from Earlier Versions
of SQL Server.
-x
Used with the
format and -f format_file options, generates an XML-based format file instead
of the default non-XML format file. The -x does not work when importing or
exporting data. It generates an error if used without both format and -f
format_file.
Computed Columns and timestamp Columns:
Values in the
data file being imported for computed or timestamp columns are
ignored, and SQL Server automatically assigns values. If the data file does not
contain values for the computed or timestamp columns in the table,
use a format file to specify that the computed or timestamp columns
in the table should be skipped when importing data; SQL Server automatically
assigns values for the column.
Computed and timestamp columns
are bulk copied from SQL Server to a data file as usual.
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.txt file.
bcp
AdventureWorks2008.HumanResources.Employee
Out
C:\Data\EmployeeData.txt
-S localhost\SqlSrv2008 –T –c –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.
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.
Importing Data into a Table:
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.
No comments:
Post a Comment