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


Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net