Bulk Inserting Data into a Database

Bulk inserting data into a database table can be a very useful operation for the following reasons:

  • You may have data stored in a file that you need to insert all at once
  • You may also want to execute a table TRIGGER once, if a cursor inputted data then a Trigger would be executed on each insert which would make the operation much less performant. Bulk insertions ensure that a TRIGGER is executed only once regardless of the amount of data inserted.

 

Methods of Bulk Insertion

1. Bulk Inserting Data from a file

Consider the scenario where we have customer details stored in a csv file and want to insert these records into a customer table in our database.

BULK INSERT Customers FROM 'C:\Customers.csv'
 WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')
 GO

In relation to executing triggers, the BULK INSERT command actually has a mechanism for executing a TRIGGER on each INSERT if required. If we wanted to execute a trigger for an insert of each row from the Customers.csv file our BULK INSERT statement would look like the following:

BULK INSERT Customers FROM 'C:\Customers.csv'
 WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRETRIGGERS)
 GO

Omitting FIRETRIGGERS means that no TRIGGERs will be fired.

 

2. Bulk Inserting Data using a temporary table

Using the same example of the Customers table like above, except this time we will be inserting data row by row into a Temp table, and then insert all the contents of this Temp table into the Customers table.

The main requirement here is that the Customers table and the CustomersTemp table must have the same definition:

DECLARE @Customers AS TABLE 
	([CustomerID] [int] NOT NULL, [Age] [int] NULL, [Data] [nvarchar](max) NULL)

DECLARE @CustomersTemp AS TABLE 
	([CustomerID] [int] NOT NULL, [Age] [int] NULL, [Data] [nvarchar](max) NULL)

For example, if we had a script that created bulk Customers, we could have a cursor that inserts each record into the @CustomersTemp temporary table. Once all the data has been inserted to this temp table, we insert the contents of the temp table into the actual table like follows:

INSERT INTO @Customers (CustomerID, Age, Data)
SELECT
CustomerID, Age, Data FROM @CustomersTemp

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s