Microsoft SQL Server 2000 Administrators Companion
3 4
The T-SQL BULK INSERT statement is similar to BCP in that both can be used to bulk copy data into the SQL Server database from a data file. Unlike BCP, however, BULK INSERT cannot be used to extract data from SQL Server databases. This limitation reduces its functionality, but because the BULK INSERT statement is run as a thread within SQL Server, the need to send data from one program to another is eliminated, which improves the performance of data loading. Thus, the BULK INSERT statement loads data more efficiently than does BCP.
BULK INSERT Syntax
Like BCP, the BULK INSERT statement has several required parameters and many optional parameters. You invoke BULK INSERT from within SQL Server (using ISQL, OSQL, or SQL Server Query Analyzer) by using the following command. (All of the required and optional parameters are shown here.)
BULK INSERT [[' database_name'. ][' owner' ]. ] {' 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 ]] [[, ] FIRETRIGGERS [ = 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 ] ) ]
Required Parameters
The location of the data file is specified by the data_file parameter. This value must be a valid path.
The database location where the data is inserted is defined by either a table definition or a view. As you can see in the preceding command, you can also specify the table or view owner, the database name, or both. If you attempt to use the BULK INSERT command to insert data into a view, you can affect only one of the base tables referenced in the FROM clause of the view.
Optional Parameters
You can use the optional parameters and keywords listed in Table 24-3 to modify the behavior of BULK INSERT. As you will see, the options available with the BULK INSERT statement are similar to those available with BCP.
Table 24-3. The optional parameters for BULK INSERT
| Optional Parameter | Description |
|---|---|
| BATCHSIZE = size | Specifies the number of rows in a batch. Each batch is one transaction. |
| CHECK_CONSTRAINTS | Specifies that constraint checking be performed. The default behavior is to ignore constraints. |
| CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ] | Specifies the code page of the data in the data file. This option is useful only with char, varchar, and text data types. |
| DATAFILETYPE [ = 'char' | 'native' | 'widechar' | 'widenative' ] | Specifies the type of data in the data file; by default, this value is char. Other options include native (native database data types), widechar (Unicode characters), and widenative (same as native, except char, varchar, and text are stored as Unicode). |
| FIELDTERMINATOR [ = field_term ] | Specifies the field terminator used with char and widechar data types. By default, this value is the tab character, \t. |
| FIRSTROW [ = first_row ] | The number of the first row to copy. The default is 1. This parameter is useful if you want to skip header information in the data file. |
| FORMATFILE [ = format_file ] | Specifies the path of a format file. |
| KEEPIDENTITY | Specifies that values for an identity column are present in the data files being imported. |
| KEEPNULLS | Specifies that empty columns retain null values. |
| KILOBYTES_PER_BATCH [ = number ] | Specifies the approximate number of kilobytes per batch used in the bulk copy. |
| LASTROW [ = last_row ] | Specifies the last row on which to perform bulk insert. The default is 0. This option is useful if you want to insert only a certain number of rows. |
| MAXERRORS [ = max_errors ] | Specifies how many errors can occur before the insert terminates. The default value is 10. |
| ORDER (column [ASC | DESC] ) | Specifies that the data in the column indicated be sorted in the specified order. |
| ROWS_PER_BATCH [ = rows_per_batch ] | Specifies the number of rows per batch. Each batch is copied as one transaction. By default, all the rows in the data file are inserted as one batch, using a single commit. You might want to specify this option when you perform bulk inserts so that table locks are released as batches are performed, thus allowing other processing to take place. |
| ROWTERMINATOR [ = row_term ] | Specifies the row terminator for char and widechar data types. The default is the newline character, \n. |
Using BULK INSERT
Now let's look at two examples of how to use the BULK INSERT statement. In both examples, we'll load data from the character file data.file (which we used in earlier examples) into the Customers table of the Northwind database.
NOTE
Remember that the BULK INSERT statement can be used only to insert data into a database; it cannot be used to extract data. Because BULK INSERT does not offer the variety of modes of operation that BCP does, only a couple of examples are provided here.
To load the data into the database, use the following T-SQL statement:
BULK INSERT Northwind..Customers FROM 'C:\data.file' WITH ( DATAFILETYPE = 'char' ) GO
You can add as many options as you want. In the following example, more optional parameters are used:
BULK INSERT Northwind..Customers FROM 'C:\data.file' WITH ( BATCHSIZE = 5, CHECK_CONSTRAINTS, DATAFILETYPE = 'char', FIELDTERMINATOR = '\t', FIRSTROW = 5, LASTROW = 20, TABLOCK )
This statement will load only rows 5 through 20 from the data file. The field terminator is specified to be the tab character (even though this is the default). This example also specifies that constraints be checked during the bulk insert process and allocates a table lock for the duration of the load. The transactions that perform the load will be done in batches of five rows each.