Website development and design blog, tutorials and inspiration

Importing CSV File Into SQL Server Using Bulk Insert

SQL Server BULK INSERT Syntax

By , Written on in SQL

Importing CSV File Into SQL Server Using Bulk Insert

292 words, estimated reading time 2 minutes.

How to import CSV file into SQL Server or how to load CSV file into SQL Server table are two commonly asked questions that have an easy answer.

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.

  1. CREATE TABLE Products
  2. (
  3. productSku VARCHAR(10),
  4. productName VARCHAR(20),
  5. productDescription VARCHAR(255),
  6. );
  7. GO

The data I am going to load is stored in the file C:\Tutorials\SQL\BulkInsertTest.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.

  1. BULK INSERT Products
  2. FROM 'C:\Tutorials\SQL\BulkInsertTest.csv'
  3. WITH
  4. (
  5. FIELDTERMINATOR = ',',
  6. ROWTERMINATOR = 'n'
  7. )
  8. 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.

  1. SELECT * FROM Products
productSkuproductNameproductDescription
ABC123Test ProductThis is a test product to test the bulk insert function
ABC987Another Test ProductThis is another test product
XYZ444Yet Another TestThis is yet another test product for SQL Server

Last updated on: Friday 8th September 2017

Comments

There are no comments for this post. Be the first!

Leave a Reply

Your email address will not be published.