A Transact-SQL command for importing data into a database is BULK INSERT. You can use BULK INSERT in much the same way that you use BCP. In fact, most of the parameters for BULK INSERT are the same as those used with BCP—they just have a different syntax. This syntax is shown in Example 10-4.
Example 10-4. BULK INSERT Syntax and Usage
Syntax
BULK INSERT [database_name.[schema_name][table_name|view_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 ]
[ [ , ] ERRORFILE = 'file_name' ]
)]Usage
BULK INSERT pubs..customers FROM 'c:datacustomer.txt ' BULK INSERT pubs..customers FROM 'c:cust.txt' with (DATAFILETYPE = 'char ', s FORMATFILE='c:cust.fmt')
In order to use BULK INSERT, you must have INSERT and ADMINISTER BULK OPERATION permissions. You may also need ALTER TABLE permission if any of the following conditions are true:
Constraints are disabled (the default setting). To keep constraints enabled, use the CHECK_CONSTRAINTS option.
Triggers are disabled (the default setting). To fire triggers, use the FIRE_TRIGGER option.
KEEPIDENTITY is used to import identity values from the specified data file.
Additionally, before using BULK INSERT, you may want to set the database recovery model to bulk-logged. This mode minimally logs bulk operations and increases performance when bulk inserting. To set this option, select a database, open its Properties dialog box, choose the Options page, and then select Bulk-Logged under Recovery Model. You may also want to use sp_tableoption to set the table lock on bulk load value. When this option is set to FALSE (the default setting), the bulk load process obtains row locks when inserting into user-defined tables. If you set this option to TRUE, the bulk load process obtains a bulk update lock instead. Members of the sysadmin fixed server role, the db_owner and db_ddladmin fixed database roles, and the table owner can modify the table lock on bulk load value.
Example 10-5 shows how to set the table lock on bulk load value using sp_tableoption.