BULK INSERT (Transact-SQL)

database_name

Is the database name in which the specified table or view resides. If not specified, this is the current database.

schema_name

Is the name of the table or view schema. schema_name is optional if the default schema for the user performing the bulk-import operation is schema of the specified table or view. If schema is not specified and the default schema of the user performing the bulk-import operation is different from the specified table or view, SQL Server returns an error message, and the bulk-import operation is canceled.

table_name

Is the name of the table or view to bulk import data into. Only views in which all columns refer to the same base table can be used. For more information about the restrictions for loading data into views, see INSERT (Transact-SQL).

'data_file'

Is the full path of the data file that contains data to import into the specified table or view. BULK INSERT can import data from a disk (including network, floppy disk, hard disk, and so on).

data_file must specify a valid path from the server on which SQL Server is running. If data_file is a remote file, specify the Universal Naming Convention (UNC) name. A UNC name has the form \\Systemname\ShareName\Path\FileName. For example, \\SystemX\DiskZ\Sales\update.txt. 

BATCHSIZE =batch_size

Specifies the number of rows in a batch. Each batch is copied to the server as one transaction. If this fails, SQL Server commits or rolls back the transaction for every batch. By default, all data in the specified data file is one batch. For information about performance considerations, see "Remarks," later in this topic. 

For more information, see Managing Batches for Bulk Import.

CHECK_CONSTRAINTS

Specifies that all constraints on the target table or view must be checked during the bulk-import operation. Without the CHECK_CONSTRAINTS option, 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 must examine the constraints on the whole table. If the table was non-empty before the bulk-import operation, the cost of revalidating the constraint may exceed the cost of applying CHECK constraints to the incremental data.

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 the invalid data.

Note
The MAXERRORS option does not apply to constraint checking.

Note
In SQL Server 2005 and later versions, BULK INSERT enforces new data validation and data checks that could cause existing scripts to fail when they are executed on invalid data in a data file.

For more information, see Controlling Constraint Checking by Bulk Import Operations.

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }

Specifies the code page of the data in the data file. CODEPAGE is relevant only if the data contains char, varchar, or text columns with character values greater than 127 or less than 32.

Note
Microsoft recommends that you specify a collation name for each column in a format file.

CODEPAGE value	Description
ACP	Columns of char, varchar, or text data type are converted from the ANSI/Microsoft Windows code page (ISO 1252) to the SQL Server code page.
OEM (default)	Columns of char, varchar, or text data type are converted from the system OEM code page to the SQL Server code page.
RAW	No conversion from one code page to another occurs; this is the fastest option.
code_page	Specific code page number, for example, 850. Important SQL Server does not support code page 65001 (UTF-8 encoding).

For more information, see Copying Data Between Different Collations.

DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' }

Specifies that BULK INSERT performs the import operation using the specified data-file type value.

DATAFILETYPE value	All data represented in:
char (default)	Character format. For more information, see Using Character Format to Import or Export Data.
native	Native (database) data types. Create the native data file by bulk importing data from SQL Server using the bcp utility. The native value offers a higher performance alternative to the char value. For more information, see Using Native Format to Import or Export Data.
widechar	Unicode characters. For more information, see Using Unicode Character Format to Import or Export Data.
widenative	Native (database) data types, except in char, varchar, and text columns, in which data is stored as Unicode. Create the widenative data file by bulk importing data from SQL Server using the bcp utility. The widenative value offers a higher performance alternative to widechar. If the data file contains ANSI extended characters, specify widenative. For more information, see Using Unicode Native Format to Import or Export Data.

FIELDTERMINATOR ='field_terminator'

Specifies the field terminator to be used for char and widechar data files. The default field terminator is \t (tab character). For more information, see Specifying Field and Row Terminators.

FIRSTROW =first_row

Specifies the number of the first row to load. The default is the first row in the specified data file. FIRSTROW is 1-based.

Note
The FIRSTROW attribute is not intended to skip column headers. Skipping headers is not supported by the BULK INSERT statement. When skipping rows, the SQL Server Database Engine looks only at the field terminators, and does not validate the data in the fields of skipped rows.

FIRE_TRIGGERS

Specifies that any insert triggers defined on the destination table execute during the bulk-import operation. If triggers are defined for INSERT operations on the target table, they are fired for every completed batch.

If FIRE_TRIGGERS is not specified, no insert triggers execute.

For more information, see Controlling Trigger Execution When Bulk Importing Data.

FORMATFILE ='format_file_path'

Specifies the full path of a format file. A format file describes the data file that contains stored responses created by using the bcp utility on the same table or view. The format file should be used if:

• The data file contains greater or fewer columns than the table or view.

• The columns are in a different order.

• The column delimiters vary.

• There are other changes in the data format. Format files are typically created by using the bcp utility and modified with a text editor as needed. For more information, see bcp Utility.

KEEPIDENTITY

Specifies that identity value or values in the imported data file are to be used for the identity column. If KEEPIDENTITY is not specified, the identity values for this column are verified but not imported 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 is to be skipped when importing data; SQL Server automatically assigns unique values for the column. For more information, see DBCC CHECKIDENT (Transact-SQL).

For more information, see about keeping identify values see Keeping Identity Values When Bulk Importing Data.

KEEPNULLS

Specifies that empty columns should retain a null value during the bulk-import operation, instead of having any default values for the columns inserted. For more information, see Keeping Nulls or Using Default Values During Bulk Import.

KILOBYTES_PER_BATCH = kilobytes_per_batch

Specifies the approximate number of kilobytes (KB) of data per batch as kilobytes_per_batch. By default, KILOBYTES_PER_BATCH is unknown. For information about performance considerations, see "Remarks," later in this topic.

For more information, see Managing Batches for Bulk Import.

LASTROW=last_row

Specifies the number of the last row to load. The default is 0, which indicates the last row in the specified data file.

MAXERRORS = max_errors

Specifies the maximum number of syntax errors allowed in the data before the bulk-import operation is canceled. Each row that cannot be imported by the bulk-import operation is ignored and counted as one error. If max_errors is not specified, the default is 10.

Note
The MAX_ERRORS option does not apply to constraint checks or to converting money and bigint data types.

ORDER ( { column [ ASC | DESC ] } [ ,... n ] )

Specifies how the data in the data file is sorted. 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, the bulk insert operation assumes the data file is unordered. For optimized bulk import, SQL Server also validates that the imported data is sorted.

For more information, see Controlling the Sort Order When Bulk Importing Data.

n

Is a placeholder that indicates that multiple columns can be specified.

ROWS_PER_BATCH =rows_per_batch

Indicates the approximate number of rows of data in the data file.

By default, all the data in the data file is sent to the server as a single transaction, and the number of rows in the batch is unknown to the query optimizer. If you specify ROWS_PER_BATCH (with a value > 0) the server uses this value to optimize the bulk-import operation. The value specified for ROWS_PER_BATCH should approximately the same as the actual number of rows. For information about performance considerations, see "Remarks," later in this topic.

For more information, see Managing Batches for Bulk Import.

ROWTERMINATOR ='row_terminator'

Specifies the row terminator to be used for char and widechar data files. The default row terminator is \r\n (newline character). For more information, see Specifying Field and Row Terminators.

TABLOCK

Specifies that a table-level lock is acquired for the duration of the bulk-import operation. 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. Holding a lock for the duration of the bulk-import operation reduces lock contention on the table, in some cases can significantly improve performance. For information about performance considerations, see "Remarks," later in this topic.

For more information, see Controlling Locking Behavior for Bulk Import.

ERRORFILE ='file_name'

Specifies the file used to collect rows that have formatting errors and cannot be converted to an OLE DB rowset. These rows are copied into this error file from the data file "as is."

The error file is created when the command is executed. An error occurs if the file already exists. Additionally, a control file that has the extension .ERROR.txt is created. This references each row in the error file and provides error diagnostics. As soon as the errors have been corrected, the data can be loaded.

For a comparison of the BULK INSERT statement, the INSERT ... SELECT * FROM OPENROWSET(BULK...) statement, and the bcp command, see About Bulk Import and Bulk Export Operations.

For information about preparing data for bulk import, such as the requirements for importing data from a CSV data file, see Preparing Data for Bulk Export or Import.

The BULK INSERT statement can be executed within a user-defined transaction. Rolling back a user-defined transaction that uses a BULK INSERT statement and BATCHSIZE clause to import data into a table or view using multiple batches rolls back all batches sent to SQL Server.

For information about when row-insert operations that are performed by bulk import are logged in the transaction log, see Prerequisites for Minimal Logging in Bulk Import.

In SQL Server 2005 and later versions, BULK INSERT enforces new, stricter data validation and data checks of data read from a file that could cause existing scripts to fail when they are executed on invalid data. For example, BULK INSERT now verifies that:

• The native representations of float or real data types are valid.

• Unicode data has an even-byte length.

Forms of invalid data that could be bulk imported in earlier versions of SQL Server, might not load now. In earlier versions of SQL Server, the failure does not occur until a client tries to access the invalid data. The stricter validation minimizes surprises when querying the data after bulk import.

Performance Considerations

Bulk Exporting or Importing SQLXML Documents

String-to-Decimal Type Conversions

In SQL Server 2005 and later versions, the string-to-decimal type conversions used in BULK INSERT follow the same rules as the Transact-SQL CONVERT function, which rejects strings representing numeric values that use scientific notation. Therefore, BULK INSERT treats such strings as invalid values and reports conversion errors.

Note
In SQL Server version 7.0 and SQL Server 2000, BULK INSERT supports string-to-decimal type conversion for strings representing numeric values that use scientific notation.

To work around this behavior, use a format file to bulk import scientific notation float data into a decimal column. In the format file, explicitly describe the column as real or float data. For more information about these data types, see float and real (Transact-SQL).

Note
Format files represent real data as the SQLFLT4 data type and float data as the SQLFLT8 data type. For more information about XML format files, see Schema Syntax for XML Format Files; or for information about non-XML format files, see Specifying File Storage Type by Using bcp.

Example of Importing a Numeric Value that Uses Scientific Notation

This example uses the following table:

Copy

CREATE TABLE t_float(c1 float, c2 decimal (5,4))

The user wants to bulk import data into the t_float table. The data file, C:\t_float-c.dat, contains scientific notation float data; for example:

Copy

8.0000000000000002E-28.0000000000000002E-2

However, BULK INSERT cannot import this data directly into t_float, because its second column, c2, uses the decimal data type. Therefore, a format file is necessary. The format file must map the scientific notation float data to the decimal format of column c2.

The following format file uses the SQLFLT8 data type to map the second data field to the second column:

<?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="\t" MAX_LENGTH="30"/>

<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30"/> </RECORD> <ROW>

<COLUMN SOURCE="1" NAME="c1" xsi:type="SQLFLT8"/>

<COLUMN SOURCE="2" NAME="c2" xsi:type="SQLFLT8"/> </ROW> </BCPFORMAT>

To use this format file (using the file name C:\t_floatformat-c-xml.xml) to import the test data into the test table, issue the following Transact-SQL statement:

Copy

BULK INSERT bulktest..t_float
FROM 'C:\t_float-c.dat' WITH (FORMATFILE='C:\t_floatformat-c-xml.xml');
GO

Requires INSERT and ADMINISTER BULK OPERATIONS permissions. Additionally, ALTER TABLE permission is required if one or more of the following is true:

• Constraints exist and the CHECK_CONSTRAINTS option is not specified.

  Note
  Disabling constraints is the default behavior. To check constraints explicitly, use the CHECK_CONSTRAINTS option.

• Triggers exist and the FIRE_TRIGGER option is not specified.

  Note
  By default, triggers are not fired. To fire triggers explicitly, use the FIRE_TRIGGER option.

• You use the KEEPIDENTITY option to import identity value from data file.

Security Account Delegation (Impersonation)

A. Using pipes to import data from a file

BULK INSERT AdventureWorks.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH 
      (
         FIELDTERMINATOR =' |',
         ROWTERMINATOR =' |\n'
      )

B. Using the FIRE_TRIGGERS argument

BULK INSERT AdventureWorks.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH
     (
        FIELDTERMINATOR =' |',
        ROWTERMINATOR = ':\n',
        FIRE_TRIGGERS
      )

C. Using line feed as a row terminator

DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT AdventureWorks.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>'' 
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)

Additional Examples

Reference

Concepts

Other Resources