Statement BULK INSERT in Transact SQL

BULK INSERT is the DML statement that allows records to be inserted into a table or view from an external file, usually a CSV file.

The syntax is as follows:

BULK INSERT table
FROM archivePath
WITH (OPTIONS)

Before carrying out a BULK INSERT we take into account the following considerations:

• The path and name of the file must be specified correctly in the FROM so that the statement can access its content.

• The order of the file data will be correlated to the columns of the table or view where we want to insert the records. This highlights the importance of respecting the consistency of the number of columns and the specific type of data that each one can store.

• The file must adapt to the implicit restrictions that define the table or view where we want to perform the insertion. This includes fields with restrictions (NOT NULL, explicit formats) or fields with property IDENTITY among others.

Options

BULK INSERT allows you to add a series of configurable options to the WITH clause when loading a file. In this section we will focus on briefly explaining the most outstanding ones:

• FIRSTROW: allows you to specify the row where the data will start to be loaded. This option allows us to skip records.

• LASTROW: allows you to specify the row that will mark the load limit, limiting the number of records that we want to insert.

• FORMAT: optional, it allows us to specify the type of file that we are going to load.

• KEEPIDENTITY: allows us to break the IDENTITY property of some field to force the system to save a specific value.

• MAXERRORS: allows you to specify the number of errors required to occur during the process before the data load stops.

• FIELDTERMINATOR: specifies the file sign that delimitates each value corresponding to each column in the view or table where we want to insert.

• ROWTERMINATOR: specifies the file sign that delimits the end of each row corresponding to each record in the view or table where we wish to insert.

Example

To illustrate how the BULK INSERT statement works, we proceed to use as an example the following CSV file that we have generated: bulkInsert.csv, whose content is as follows:

9;John;Mackay;58 Lamberts Branch Road;123777898
10;Aaron;Gallagher;2218 Smith Road;556814332

We want, therefore, to insert these new values into the Customers table. To do this, we leave the execution script:

BULK INSERT Customers
FROM
    'C:\Users\user\Desktop\WEB\bulkInsert.csv'
WITH (
    FORMAT = 'CSV',
    FIELDTERMINATOR= ';',
    ROWTERMINATOR = '\n',
    KEEPIDENTITY,
    MAXERRORS = 2
    );

Since our Customers table is defined with an auto-incrementing IDENTITY property 1 to 1 for the customerId field, we need to use the KEEPIDENTITY option to break that property and insert the values defined in the file.

Once the statement has been executed, we check that the records have been correctly inserted into the Customers table:

    SELECT cutomerId,
           [name],
           [lastName],
           [address],
           bankAccount
    FROM Customers
    WHERE customerId IN (9,10);