Using BULK INSERT is a nice and efficient way to, well, do a bulk insert! It’s can be good in situations where you have many, many records to insert – to many to fit into Excel, for example.

To do a BULK INSERT, simply upload your file to the server that holds the database you’re going to be inserting into. Put the file in a relatively simple location, unless you like typing long file paths! Alternatively, you can copy and paste the file path, of course.

Below is the structure of the example table I’ll be using, along with the contents of the file which will be imported.

--Table
 
CREATE TABLE dbo.Orders
(
OrderId INT NOT NULL,
PersonId INT NOT NULL,
Item VARCHAR(50) NOT NULL
)

--Contents of Orders.txt

4, 1, Keyboard
5, 1, Mouse

Once your file is in place, you’re ready to roll.

BULK INSERT dbo.Orders
FROM 'C:\Orders.txt'
WITH
(
FIELDTERMINATOR = ','
ROWTERMINATOR = '\n'
)

Notice the WITH statment after specifying the file location. I have only used a couple of the options available to the BULK INSERT statement. I will explain these, but a for a full list of options – see the MSDN site on BULK INSERT.

The two options I’ve used in the example are the only two that you should need to insert some relatively simple data.

FIELDTERMINATOR is used to specify the character used to split your data into the columns they belong to, you need to have the columns in the correct order in the file so that they are mapped correctly. Obviously, you need to pick a character that isn’t going to appear in any of the columns.

ROWTERMINATOR is used to specify what denotes a new row. In my example, I’ve used \n, which is equal to an enviromental newline. This tells SQL that any time there is a new line in the Orders.txt file, that this should be treated as a seperate row from the line above.

Once executed, a message will appearing telling you how many rows have been imported – just like a normal insert statement.