Importing CSV File Into SQL Server Using Bulk Insert
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.
- CREATE TABLE Products
- productSku VARCHAR(10),
- productName VARCHAR(20),
- productDescription VARCHAR(255),
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.
- BULK INSERT Products
- FROM 'C:\Tutorials\SQL\BulkInsertTest.csv'
- FIELDTERMINATOR = ',',
- ROWTERMINATOR = 'n'
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
|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|