Importing/Exporting Data  «Prev  Next»

Lesson 4The bulk insert statement
ObjectiveUse the bulk insert statement.

Bulk Insert Statement in SQL Server

In SQL Server 7.0, Microsoft added the capability to load data from a file into a table using the Transaction SQL extension, bulk insert statement. This following Diagram illustrates the syntax of the bulk insert statement.
Bulk Insert
  1. The table that you're loading data into.
  2. The file containing the data.
  3. Specifies how many rows to load as one transaction.
  4. Tells SQL Server to check column constraints when loading data.
  5. Specifies the meaning of characters with an ascii value less than 32 or greater than 127.
  6. Specifies that the data in the file is all character, native datatypes, unicode characters, or unicode with native datatypes.
  7. For character or wide character datatypes, specifies the field separator; this defaults to '\t' a tab
  8. The first row in the file to load.
  9. Allows you to specify a file that describes the format of the data file.
  10. Specifies to use the values in the data file for identity columns.
  11. Specifies that empty columns gets a null assigned rather than the columns default value.
  12. Specifies how many kilobytes will be in a batch.
  13. The last row to load.
  14. Specifies to abort the bulk insert after max_errors have been reached; this option defaults to 10.
  15. Specifies the order of the data int the file.
  16. Specifies the number of rows per batch.
  17. Specifies the row terminator when load char or widechar data; this defaults to \r a carriage return.
  18. Tells SQL Server to lock the whole table.

Bulk Insert Statment in SQL Server

Bulk Insert Statement consisting of 1) table_name 2) data_file and 3) BATCHSIZE
Bulk Insert Statement consisting of 1) table_name 2) data_file and 3) BATCHSIZE

  1. table_name– The table that you are loading data into
  2. data_file – The file containing the data
  3. [ BATCHSIZE [= batch_size]] – Specifies how many rows to load as one transaction
  4. [[,] CHECK_CONSTRAINTS] – Tells SQL Server to check column constraints when loading data
  5. [[,] CODEPAGE [= 'ACP' | 'OEM' | 'RAW' | 'code_page']] – Specifies the meaning of characters with an ascii value less than 32 or greater than 127
  6. [[,] DATAFILETYPE [= {'char' | 'native'| 'widechar' | 'widenative'}]] – Specifies that the data in the file is all character, native datatypes, unicode characters, or unicode with native datatypes
  7. [[,] FIELDTERMINATOR [= 'field_terminator']] – For character or wide character datatypes, specifies the field separator; this defaults to ‘\t’ a tab
  8. [[,] FIRSTROW [= first_row]] – The first row in the file to load
  9. [[,] FORMATFILE [= 'format_file_path']] – Allows you to specify a file that describes the format of the data file
  10. [[,] KEEPIDENTITY] – Specifies to use the values in the data file for identity columns
  11. [[,] KEEPNULLS] – Specifies that empty columns gets a null assigned rather than the columns default value
  12. [[,] KILOBYTES_PER_BATCH [= kilobytes_per_batch]] – Specifies how many kilobytes will be in a batch
  13. [[,] LASTROW [= last_row]] – The last row to load
  14. [[,] MAXERRORS [= max_errors]] – Specifies to abort the bulk insert after max_errors have been reached; this option defaults to 10
  15. [[,] ORDER ({column [ASC | DESC]} [,...n])] – Specifies the order of the data int the file
  16. [[,] ROWS_PER_BATCH [= rows_per_batch]] – Specifies the number of rows per batch
  17. [[,] ROWTERMINATOR [= 'row_terminator']] – Specifies the row terminator when load char or widechar data; this defaults to‘\r’ a carriage return
  18. [[,] TABLOCK] – Tells SQL Server to lock the whole table


Notes about parameters

When using the bulk insert statement, these optional parameters affect how data is sent to SQL Server:
  1. ROWS_PER_BATCH: The whole file is loaded as one transaction with each batch sent to SQL Server as a unit. This parameter also controls how many rows are sent to SQL Server as a group.
  2. BATCHSIZE: SQL Server will send each batch in its own transaction.
Note that if you use both the ROWS_PER_BATCH and the BATCHSIZE parameter the BATCHSIZE parameter will override the ROWS_PER_BATCH.
When specifying the order of the data with the ORDER option, you have to understand how SQL Server sorts the data based on its collation sequence. If the file is not sorted correctly, or if the table does not have a clustered index in the columns specified, the ORDER option will be ignored.
Collation sequence: Determines the order in which unicode data is sorted.

Specifying a format file

If you need more control over the format of the data being loaded, SQL Server allows you to specify a format file. A format file allows you to load only specified fields from a file into a table.
A format file is useful under the following conditions:
  1. The data file contains a different number of columns than the table or view.
  2. The data file is in a different order than the table’s or view’s columns.
  3. The column delimiters vary.
Later in this module, you will learn to use the bulk copy program to generate and edit a format file. The following tooltip illustrates a format file.

Apply, Filter, Sort
  1. The version of bcp program used.
  2. Number of fields in the file.
  3. Order of the field in the file.
  4. The host data type. It can be SQLCHAR, SQLNCHAR, SQLBINARY, SQLDATETIME, SQLDATETIM4, SQLDECIMAL, SQLNUMERIC, SQLFLT8, SQLFLT4, SQLINT, SQLSMALLINT, SQLTINYINT, SQLMONEY, SQLMONEY4, SQLBIT, SQLUNIQUEID, or SQLBINARY.
  5. The length of the column prefix, either 0, 1,2 or 4.
  6. The length of the field in the file.
  7. The field terminator.
  8. The order of the column in the table.
  9. Description column. This column usually contains the field name and cannot be blank.

Bulk Format File
To skip a field from the data file, specify prefix length of 0, server column order of 0, and no terminator.
The next lesson will cover how to use the bcp program to load and extract data from SQL Server.

Loading Data - Quiz

Click the Quiz link below to see how well you have mastered loading data with SQL Statements.
Loading Data - Quiz