Data Management

Bulk importing data into SQL Server

BULK INSERT is a TSQL command used in SQL Server to load an external file into a database table for using a specified format. Learn how to import data using the command, and find out how changing some of its options can make it more efficient to insert data.

If you work with databases, at some point, you'll deal with inserting data into SQL Server from outside data files. This tutorial shows how to import data using the BULK INSERT command and explains how changing some of its options can make it easier and more efficient to insert data.

BULK INSERT

BULK INSERT is a TSQL command used in SQL Server to load an external file into a database table using a specified format. This gives the developer the ability to import directly into database tables without using an outside program, such as Integration Services. Although BULK INSERT doesn't allow any complex logic or transformations, it does offer useful options in terms of formatting the data and how the import is accomplished. One limitation to BULK INSERT is that it only puts data into SQL Server.

Inserting data

The following example will give you a better idea of how to use the BULK INSERT command. First, I'll create the SalesHistory table; this is where I insert data from the text file.

CREATE TABLE [dbo].[SalesHistory]

(

[SaleID] [int],

[Product] [varchar](10) NULL,

[SaleDate] [datetime] NULL,

[SalePrice] [money] NULL

)

When BULK INSERT is used to insert data into a database table, triggers on that table will not be fired by default. This is convenient because triggers slow down the import process.

In the next example, I create a trigger on the SalesHistory table that prints out the number of records inserted into the table.

CREATE TRIGGER tr_SalesHistory

ON SalesHistory

FOR INSERT

AS

BEGIN

PRINT CAST(@@ROWCOUNT AS VARCHAR(5)) + ' rows Inserted.'

END

The file I use to insert data is a text file with the values separated by commas. This file has 1,000 records in it, and the fields relate directly to the fields in the SalesHistory table I just created.

This script uses BULK INSERT to insert the data in the SalesHistoryText table into the SalesHistory table. Because the values in the text file are comma seperated, all I need to specify for the data to be inserted is the FIELDTERMINATOR. Notice that the trigger I just created is not fired when this statement is run:

BULK INSERT SalesHistory FROM 'c:\SalesHistoryText.txt' WITH (FIELDTERMINATOR = ',')

Sometimes it is necessary to fire triggers when importing a large chunk of data. The following script uses the FIRE_TRIGGERS option to indicate that any triggers on the destination table should be fired:

BULK INSERT SalesHistory FROM 'c:\SalesHistoryText.txt' WITH (FIELDTERMINATOR = ',', FIRE_TRIGGERS)

You can use the BATCHSIZE command to set how many records will be inserted into the table as a single transaction. In the previous example, all 1,000 records were inserted into the table in the same transaction. In this example, I set the BATCHSIZE to 2, which means that 500 individual transactions will be committed on the table. This also means that the trigger will fire 500 times, so 500 prints statements will be output to the screen.

BULK INSERT SalesHistory FROM 'c:\SalesHistoryText.txt' WITH (FIELDTERMINATOR = ',', FIRE_TRIGGERS, BATCHSIZE = 2)

BULK INSERT is not limited to locally mapped drives in SQL Server 2005. The following statement shows how you can import data from the SalesHistoryText file from the D drive on a server named FileServer.

BULK INSERT SalesHistory FROM 'FileServerD$SalesHistoryText.txt' WITH (FIELDTERMINATOR = ',')

Sometimes it is nice to view the data you are importing before committing it to a database table. The following statement uses the OPENROWSET function, along with the BULK command, to read data from the SalesHistoryText text file. This statement also uses a format file that indicates how the data in the text file is formatted.

    SELECT *

FROM OPENROWSET(BULK 'c:\SalesHistoryText.txt' ,

FORMATFILE='C:\SalesHistoryFormat.Xml'

) AS mytable;

GO

Bulk Copy Program

You can use the Bulk Copy Program (BCP) utility to insert and export data from SQL Server. Read blogger Steven Warren's article about how to use BCP in SQL Server.

Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

-----------------------------------------------------------------------------------------

Get database tips in your inbox

TechRepublic's free Database Management newsletter, delivered each Tuesday, contains hands-on SQL Server and Oracle tips and resources. Automatically subscribe today!

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

11 comments
yeahcoy
yeahcoy

Thanks...this was a great post...and it was really easy to follow..thank you.

anilsinghal1
anilsinghal1

my txt file is like this. please tell me how to bulk insert. I am pasting three rows only so you could see the pattern and my table has 13 columns (everything is in single row): ???Time Stamp???,???Capitl???,???Centrl???,???Dunwod???,???Genese???,???Hud Vl???,???Longil???,???Mhk Vl???,???Millwd???,???NYC.???,???North???,???West???,???NYISO??? ???10/08/2008 00:00???,1000,1403,483,865,878,1776,655,203,4766,703,1463,14195 ???10/08/2008 01:00???,948,1356,451,828,839,1642,618,192,4461,695,1407,13437 ???10/08/2008 02:00???,913,1308,432,806,821,1582,603,187,4229,691,1392,12964 ???10/08/2008 03:00???,906,1295,419,802,815,1556,602,185,4114,690,1387,12771

L A Lester
L A Lester

Hello this is some great info! I'm loving the website as well. I am new to the SQL world and I have attempted to try this "How To" template. I'm stuck at importing the text file and I did see where it should be referenced as c:\SalesHistoryText.txt. I am getting a message that the path cannot be identified. Does anyone know what I am doing wrong? Thanks,

namkan2006
namkan2006

Very useful. Tim Chapman's explanation is precise.

chapman.tim
chapman.tim

Please note that any reference to the document c:SalesHistoryText.txt should actually be: c:\SalesHistoryText.txt Thanks, Tim

chapman.tim
chapman.tim

Make sure that is where you saved the downloaded file....

poorbass
poorbass

It assumes that you DO have a file c:\yadayadayada.txt right?

L A Lester
L A Lester

I save the file in on my C drive in a temp folder but when I run the script it tells me that the path does not exist. How can I save the file to my SQL server?

L A Lester
L A Lester

Thanks Poorbass I figured out how to copy the file to the server! It worked for me! Thanks for trying to help! Believe me I am a TRUE beginner!

L A Lester
L A Lester

Thanks Poorbass I figured out how to copy the file to the server! It worked for me! Thanks for trying to help! Believe me I am a TRUE beginner!

L A Lester
L A Lester

Yes- I was told that I need to add the file to my SQL server but that's what I'm stomped on. It's not identifying the file from my PC