In one of my recent projects I needed to import data from tab delimited files into an SQL Server database. I decided to go with the Bulk Insert option. Bulk Insert is a neat little T-SQL function that lets you import tabular data from a structured file to a corresponding database table.
Here’s an example of how to use Bulk Insert
BULK INSERT Customer
FROM ‘c:\import\customer.txt’
WITH
(
KEEPNULLS,
FIELDTERMINATOR =’\t’,
ROWTERMINATOR =’ \n’
)
The above code samples imports tabular customer data from customer.txt, and inserts it into the Customer table. It’s important that the column in both the file and the table are in the same order.
My project called for a tab delimited file, but you can use whatever delimiter you like. Other typical formats you might see are comma separated files (csv) and | separated files.
One thing to keep in mind: Don’t represent null values using some variation of a “null” – string since this will be seen as a regular string, which will fail if you try to insert it into a non char column (or a char column too small to hold your null string). You should instead leave it as an empty value in your delimited file.
More information and documentation can be found here:
http://msdn.microsoft.com/en-us/library/ms188365.aspx
Tags: Bulk Insert, Sql Server
