The BULK INSERT Statement (Transact-SQL)

The BULK INSERT Statement ( Transact -SQL)

BULK INSERT allows the bulk load of data into a database table via transact-SQL. The main difference between this statement and BCP is that BULK INSERT is for loads only (and is SQL code), whereas BCP is a bidirectional, command-line “based utility.

The syntax for the BULK INSERT statement is shown here:

 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 ] ) ] 

As you can see, most of the options for this statement are the same as (or similar to) the switches for the BCP utility. So you can think of BULK INSERT as the SQL code version of BCP IN .

The CODEPAGE option is used when you need to load extended characters (values greater than 127); this option allows you to specify one of the following values for char , varchar , and text datatypes:

ACP	Convert from the ANSI/Microsoft Windows code page (ISO 1252) to the SQL Server code page.
OEM	Convert from the system OEM code page to the SQL Server code page. This is the default.
RAW	No conversion, which makes this the fastest option.
<value>	Specific code page number (for example, 850 for the 4.2x default code page). For a list of the available code pages, look under the "Code Pages and Sort Orders" entry in the SQL Server Books Online.

The DATAFILETYPE option allows the specification of the data character set:

char	Data is in ASCII format.
native	Data is in SQL Server native format.
widechar	Data is in Unicode format.
widenative	Data is native, except for the c har , varchar , and text columns , which are stored as Unicode.

This last option, widenative , is used when you need to transfer extended characters but want the performance offered by native data files.

You can easily use isql or Query Analyzer to execute the following sample statement. This example loads customer data from the file c:\temp\customer.dat into the customers table in the Northwind Database:

 BULK INSERT northwind..customers  FROM 'd:\customers.dat' WITH ( FORMATFILE = 'd:\customers.fmt'  )