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 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.
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]
[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
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.
FROM OPENROWSET(BULK 'c:\SalesHistoryText.txt' ,
) 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 firstname.lastname@example.org.
————————————————————————————————————————————-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!
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.