Importing CSV File Into SQL Server Using Bulk Insert

A CSV file is one that has one row per line, and column values separated with a common, which gives it the name Comma Separated Values. It does not necessarily have to be comma separated, it could be separated with tabs or some other delimiter. CSV files are almost universally supported and a good way of transporting database records between systems.

To import a CSV into SQL Server you must first create the table. In this example the table is called Products and has three columns, product sku, product name and product description.

CREATE TABLE Products
(
  productSku VARCHAR(10),
  productName VARCHAR(20),
  productDescription VARCHAR(255),
);
GO

The data I am going to load is stored in the file C:\Tutorials\SQLBulkInsertTest.csv, the contents of which are shown below.

ABC123,Test Product,This is a test product to test the bulk insert function
ABC987,Another Test Product,This is another test product
XYZ444,Yet Another Test,This is yet another test product for SQL Server

Now, to import this data into the test Products table, we are going to use the Bulk Insert command.

BULK INSERT Products
FROM 'C:TutorialsSQLBulkInsertTest.csv'
WITH
(
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = 'n'
)
GO

Where FIELDTERMINATOR is the character used to separate column values and ROWTERMINATOR is used to split records. In this case the defaults are to split values using a comma and new line marks the end of a record.

By viewing the table data now, we can see that the data has been successfully imported.

SELECT * FROM Products