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 char, 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'  )


    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features
     
    ASPTreeView.com
     
    Evaluation has М№ЪА¶Чexpired.
    Info...