Quickly load data with Bulk Insert

Did you ever have a csv file that somebody wanted loaded into the database so they could do a quick analyses on it? You could create a SSIS project or use the SSMS import data task, but here is a nice quick and easy way to do it using TSQL.

Below is a file that the analyst needs loaded. It is just a simple comma separated file.

Now I need to create a table to to load the csv file contents into. It has two fields to match the file. You can also load to a pre-existing table if the columns match the csv file.

Now by using the BULK INSERT command, it reads and loads the file into the table.

BULK INSERT dbo.jim_import
FROM 'c:\temp\jim_import.csv'
WITH
(
	FIRSTROW = 2,
	FIELDTERMINATOR = ',',  
	ROWTERMINATOR = '\n',   
	TABLOCK,
	CODEPAGE = 'ACP'
);

Now for some explanations on the parameters in the WITH section.

FIRSTROW is what row in the file it should start importing from. We have a header row in our file so the data starts on line 2.

FIELDTERMINATOR is what the separator is between fields, a comma in our case.

ROWTERMINATOR is the row terminator to be used for char and widechar data files.

TABLOCK is used to lock the table during the load. This and the other parameters can be changed if needed.

CODEPAGE is for specifying what format your data will be in.

For all other parameters that are available, please look on Books Online for the dozens of different options. https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver15

Lets look at the table now, and it has the items from the file imported in!

We can also load a file that has a pipe separator and skip the first few rows. This file we are going to only load the bottom two rows.

The two parameters that I changed are the FIRSTROW to start at line 4 and the FIELDTERMINATOR is now a |. After running the statement, it says two rows got imported. Lets take a loook.

There we go. It only loaded the bottom two rows.

There are multiple ways to do things in SQL Server and now you know another way to import data into a table.