Bulk Insert Command Details

Bulk Insert Command Detail Collection

BULK INSERT

Copies a data file into a database table or view in a user-specified format.

Grammar

BULK INSERT [[' database_name '.] [' owner '].] {' table_name ' from ' data_file '}

[With

(

[BatchSize [= Batch_size]]

[[,] check_constraints]

[[,] CODEPAGE [= ' ACP ' | ' OEM ' | ' RAW ' | ' Code_page ']]

[[,] datafiletype [=

{' Char ' | ' Native ' | ' Widechar ' | ' Widenative '}]]

[[,] fieldterminator [= ' field_terminator ']]

[[,] firstrow [= First_row]]

[[,] fire_triggers]

[[,] formatfile = ' Format_file_path ']

[[,] keepidentity]

[[,] keepnulls]

[[,] kilobytes_per_batch [= Kilobytes_per_batch]]

[[,] lastrow [= Last_row]]

[[,] maxerrors [= max_errors]]

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

[[,] rows_per_batch [= Rows_per_batch]]

[[,] rowterminator [= ' row_terminator ']]

[, [TABLOCK]]

)

]

Parameters

' database_name '

is the name of the database that contains the specified table or view. If not specified, the system defaults to the current database.

' Owner '

is the name of the owner of the table or view. Owner is optional when the user performing the bulk copy operation has the specified table or view. If owner is not specified and the user performing the bulk copy operation does not have the specified table or view, Microsoft®sql Server™ returns the error message and cancels the bulk copy operation.

' table_name '

is the name of the table or view in which the bulk copy data is located. You can only use all of those columns to refer to the view that contains the same base table. For more information about the limitations of copying data to a view, see INSERT.

' Data_file '

is the full path to the data file that contains the data to be copied to the specified table or view. BULK inserts copy data from the disk (including network, floppy disk, hard disk, etc.).

Data_file must specify a valid path from the server that is running SQL Server. If Data_file is a remote file, specify a Universal naming convention (UNC) name.

batchsize [= Batch_size]

Specifies the number of rows in the batch. Each batch is replicated to the server as a transaction. SQL Server commits or rolls back (on failure) each batch of transactions. By default, all data in the specified data file is a batch.

Check_constraints

Specifies that any constraints on TABLE_NAME are checked during bulk copy operations. By default, constraints are ignored.

CODEPAGE [= ' ACP ' | ' OEM ' | ' RAW ' | ' Code_page ']

Specifies the code page for the data in the data file. CODEPAGE is applicable only if the data contains char, varchar, or text columns with a character value greater than 127 or less than 32.

CODEPAGE Value Description

Columns of ACP char, varchar, or text data types are converted from the Ansi/microsoft Windows® code page ISO 1252 to the SQL Server code page.

The columns of the OEM (default) char, varchar, or text data type are converted from the System OEM code page to the SQL Server code page.

RAW does not convert from one code page to another; This is the fastest option.

Code_page a specific code page number, such as 850.

DataFileType [= {' Char ' | ' Native ' | ' Widechar ' | ' Widenative '}]

Specifies that BULK inserts perform the copy operation using the specified default value.

DataFileType Value Description

char (the default value) performs a bulk copy operation from a data file that contains character data.

Native uses native (database) data types to perform bulk copy operations. The data file to be loaded is created by bulk copy data, which is done from SQL Server using the bcp utility.

Widechar performs a bulk copy operation from a data file that contains Unicode characters.

Widenative performs the same bulk copy operation as native, where the char, varchar, and text columns are stored as Unicode in the data file. The data file to be loaded is created by bulk copy data, which is done from SQL Server using the bcp utility. This option is a more high-performance alternative to the WIDECHAR option, and it is used to transfer data from a computer running SQL Server to another computer using a data file. This option is used to take advantage of the performance of the native mode when data that contains ANSI extended characters is passed.

fieldterminator [= ' field_terminator ']

Specifies the field terminator used for char and widechar data files. The default field terminator is/t (tab).

FirstRow [= First_row]

Specifies the line number of the first row to be copied. The default value is 1, which indicates the first row in the specified data file.

Fire_triggers

Specifies that any insert triggers defined in the destination table will be executed during the bulk copy operation. If Fire_triggers is not specified, no insert triggers are executed.

formatfile [= ' Format_file_path ']

Specifies the full path of a format file. The format file describes the data files that contain the stored responses that were created in the same table or view using the bcp utility. The format file should be used in the following situations:

A data file contains more or fewer columns than a table or view.

Columns are in different order.

The column delimiter changes.

There are other changes to the data format. Typically, a format file is created by using the bcp utility and modified with a text editor as needed. For more information, see bcp utility.

KeepIdentity

Specifies that the value of the identity column exists in the import file. If keepidentity is not specified, the identity value of this column in the imported data file is ignored, and SQL Server automatically assigns a unique value based on the seed and increment values specified when the table was created. If the data file does not contain an identity column in the table or view, use a format file to specify that the identity column in the table or view should be ignored when the data is imported, and that SQL Server automatically assigns a unique value to this column. For more information, see DBCC checkident.

Keepnulls

Specifies that empty columns in bulk copy operations should retain a null value instead of assigning a default value to the inserted column.

Kilobytes_per_batch [= Kilobytes_per_batch]

Specifies the approximate kilobytes (KB) of data in each batch. By default, Kilobytes_per_batch is unknown.

LastRow [= Last_row]

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

maxerrors [= Max_errors]

Specifies the maximum number of errors that may occur before the bulk copy operation is canceled. Each row that cannot be imported by a bulk copy operation is ignored and counted as an error. If max_errors is not specified, the default value is 0.

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

Specifies how the data in the data file is sorted. If the loaded data is sorted based on the clustered index in the table, the performance of the bulk copy operation can be increased. If the data file is sorted based on a different order, or if there is no clustered index in the table, the ordering clause is ignored. The given column name must be a valid column in the destination table. By default, the bulk insert operation assumes that the data file is not sorted.

N

Represents a placeholder that can specify multiple columns.

Rows_per_batch [= Rows_per_batch]

Specifies the number of rows (that is, rows_per_bacth) for each batch of data processed. Used when no batchsize is specified, causing the entire data file to be sent to the server as a single transaction. The server optimizes the bulk load according to the rows_per_batch. By default, Rows_per_batch is unknown.

rowterminator [= ' row_terminator ']

Specifies the line terminator to use for char and widechar data files. The default value is/n (line break).

TABLOCK

Specifies that a table-level lock is acquired for the bulk copy operation. If the table is not indexed and TABLOCK is specified, the table can be loaded at the same time by multiple clients. By default, the locking behavior is determined by the Table option table lock on bulk load. Controlling locks only during bulk copy operations reduces lock contention on the table and greatly improves performance.

Comments

The BULK INSERT statement can be executed in a user-defined transaction. For a user-defined transaction that uses a BULK INSERT statement and a batchsize clause to load data into a table or view that uses multiple batches, rolling back it rolls back all batches sent to SQL Server.

Permissions

Only the sysadmin and bulkadmin fixed server role members can perform BULK inserts.

Example

This example imports order details from the specified data file using the vertical bar (|) character as the field terminator, using |/n as the line terminator.

BULK INSERT northwind.dbo. [Order Details]

From ' F:/orders/lineitem.tbl '

With

(

FieldTerminator = ' | ',

Rowterminator = ' |/n '

)

This example specifies the Fire_triggers parameter.

BULK INSERT northwind.dbo. [Order Details]

From ' F:/orders/lineitem.tbl '

With

(

FieldTerminator = ' | ',

Rowterminator = ': N ',

Fire_triggers

)

=============================================================

BULK INSERT

[database_name. [Schema_name]. | Schema_name. ] [table_name | view_name]

From ' Data_file '

[With

(

[[,] batchsize = batch_size]--batchsize directive to set the number of records that can be inserted into a table in a single transaction

[[,] check_constraints]--Specifies that all constraints on the target table or view must be checked during a bulk-import operation. Without the check_constraints option, all CHECK and FOREIGN KEY constraints are ignored, and the table's constraints are marked as untrusted after this operation.

[[,] CODEPAGE = {' ACP ' | ' OEM ' | ' RAW ' | ' Code_page '}]--Specifies the code page for the data in the data file

[[,] DataFileType =

{' Char ' | ' Native ' | ' Widechar ' | ' Widenative '}]--Specifies that BULK inserts perform the import operation using the specified data file type value.

[[,] fieldterminator = ' field_terminator ']--identifies the symbol that separates the content

[[,] firstrow = First_row]--Specifies the line number of the first row to be loaded. The default value is the first row in the specified data file

[[,] fire_triggers]--triggers are started

[[,] formatfile = ' Format_file_path ']

[[,] keepidentity]--Specifies that identity values in the import data file are used to identify columns

[[,] keepnulls]--Specifies that empty columns should retain a null value during a bulk-import operation without inserting any default values for the column

[[,] kilobytes_per_batch = Kilobytes_per_batch]

[[,] lastrow = Last_row]--Specifies the line number of the last line to load

[[,] maxerrors = max_errors]--Specifies the maximum number of syntax errors that are allowed to occur in the data, and the bulk import operation is canceled after that number.

[[,] Order ({column [ASC | DESC]} [,... N]]--Specify how data in the data file is sorted

[[,] rows_per_batch = Rows_per_batch]

[[,] rowterminator = ' row_terminator ']--identifies the symbol for the separator line

[[,] TABLOCK]--Specifies that a table-level lock is acquired for the duration of the bulk-import operation

[[,] errorfile = ' file_name ']--Specifies a file for collecting rows that are malformed and cannot be converted to an OLE DB rowset.

)]

Here are a few examples of simple applications

Bulk Insert Xsxt.dbo.tabletest from ' c:/data.txt '

With

Fieldterminator= ', ',

Rowterminator= '/n '

)