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.
When using the bulk insert statement, these optional parameters affect how data is sent to SQL Server:
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.
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:
The data file contains a different number of columns than the table or view.
The data file is in a different order than the table’s or view’s columns.
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.
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