Load text or csv file data into SQL Server

Today I will talk about loading a CSV or comma or txt file into SQL Server using BULK INSERT.

Using BULK INSERT, in few steps you can load the file into database.

Let’s go with an example

Let’s say I have following data in a file.

CustomerID,Customer_Name, Customer_Location
1,SAM,NY
2,BEN,NJ
3,HARY,HYD

Now the task is to load this file into SQL Server table. To do this, create one table in SQL Server with the similar structure.

CREATE TABLE Cusomer
(
CustomerID INT,
Customer_Name VARCHAR(50),
Customer_location VARCHAR(10)
)

Structure is exactly the same as the file. Use the below query to load the data.

BULK INSERT Cusomer
FROM 'c:\vijay_sql\Customer.txt'
WITH
(
FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

Important Parameters:
1) FROM ==> this is to specify the File Location from which we want to load the data.
2) FIRSTROW ==> This is to specify which row we want to load the data. This field is not about skipping the header.
3) MAXERRORS ==> This is to specify the maximum number of syntax errors allowed in the data before the bulk-import operation is canceled. Each row that cannot be imported by the bulk-import operation is ignored and counted as one error. If this field is not specified at all, then default is 10. In this case this value is set to 0 which means if there is a single error then, don’t load the data.
4) FILEDTERMINATOR ==> this is to specify how fields are separated. In this case field separator is “,”.
5) ROWTERMINATOR ==> this is to specify how to differentiate the different rows. In our case it’s the new line character.

There are various options available with the BULK INSERT like Fire the Triggers or Keep the Identity etc. I will show you some flavors/options of BULK INSERT in my next postings.