Introduction to SQL Server 2008 Data Import with BULK IMPORT

There are several methods for importing data from a text file, such as a comma or tab separated values file, into a SQL Server database. This post will provide a brief walk though demonstrating the basics of the BULK INSERT statement. The BULK INSERT statement can be used in scenarios where you need to import the contents of a text file into a table. For example, you might receive new records in batches rather than individually. In this scenario it makes sense to import the batch at once rather than executing an insert for each record in the batch. In this post I will import a list of contacts containing names (first and last) and phone numbers into a table named "Contacts". I will make text file used in the post available for download.

Here is the T-SQL used to create the Contacts table.

CREATE TABLE Contacts
(
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
PhoneNumber VARCHAR(25) NOT NULL
);

I have a CSV (comma seperated values) text file containing two handle sample contact entries that I wish to import. The first row contains the column headings and each subsequent row contains the three column values separated by commas. Below is a sample of the file's contents.

In order to import these records into my Contacts table I will use SQL's BULK INSERT statement as it appears below:

BULK INSERT Contacts
FROM 'C:\sampledata\source.csv'
WITH
(
FIELDTERMINATOR = ',',
FIRSTROW = 2
);

The destination table "Contacts" is specified first after the "BULK INSERT" keywords. The path to the CSV target file is specified next after the "FROM" keyword. The "WITH" clause allows us to customize how the data will be imported. In this case I have used the "FIELDTERMINATOR" option to indicated that a comma is the column separator for our file. The default is the tab character. I have used the "FIRSTROW" option to skip the first line of the CSV file since it contains column headings which do not need to be imported.

Below is a screen shot of the contents of the "Contacts" table after the import.

This is a very basic example. In this case the Contacts table and the CSV file contain exactly the same columns in exactly the same order. If there were differences between the layout of the table or CSV file additional options would need to be specified in the "WITH" clause.

References

The BULK INSERT statement from SQL Server 2008 Books Online:

http://msdn.microsoft.com/en-us/library/ms188365.aspx

The sample data was created using Benjamin Keen's online data generator:
http://www.generatedata.com

Download the sample CSV file:
http://www.wardlawclaims.com/attachments/blog/jeremy/Introduction-to-SQL-Server-2008-Data-Import-with-BULK-IMPORT/source.csv