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
If the number of pages to be flushed in a single batch exceeds an internal threshold, a full scan of the buffer pool might occur to identify which pages to flush when the batch commits. This full scan can hurt bulk-import performance. A likely case of exceeding the internal threshold occurs when a large buffer pool is combined with a slow I/O subsystem. To avoid buffer overflows on large machines, either do not use the TABLOCK hint (which will remove the bulk optimizations) or use a smaller batch size (which preserves the bulk optimizations).
Because computers vary, we recommend that you test various batch sizes with your data load to find out what works best for you.
Bulk Exporting or Importing SQLXML Documents
To bulk export or import SQLXML data, use one of the following data types in your format file:
|
Data type
|
Effect
|
|---|
|
SQLCHAR or SQLVARYCHAR
|
The data is sent in the client code page or in the code page implied by the collation). The effect is the same as specifying the DATAFILETYPE = 'char' without specifying a format file.
|
|
SQLNCHAR or SQLNVARCHAR
|
The data is sent as Unicode. The effect is the same as specifying the DATAFILETYPE = 'widechar' without specifying a format file.
|
|
SQLBINARY or SQLVARYBIN
|
The data is sent without any conversion.
|
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.
.gif) 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).
Example of Importing a Numeric Value that Uses Scientific Notation
This example uses the following table:
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:
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:
BULK INSERT bulktest..t_float
FROM 'C:\t_float-c.dat' WITH (FORMATFILE='C:\t_floatformat-c-xml.xml');
GO