Import CSV file into SQL Server

 

 

Importing data from csv file to SQL server db.

BULK INSERT TableName FROM 'csv file path' WITH(FIRSTROW = 2, MAXERRORS = 0, FIELDTERMINATOR = ',' )

(Inserts csv file data to specified table Name )

 

FIRSTROW = 2                – Start import from this row

MAXERRORS = 0              – max number of syntax errors allowed

FIELDTERMINATOR = ','     –Char that separates Fields

There are some other parameters available like KEEPIDENTITY , KEEPNULLS , LASTROW , ROWTERMINATOR  etc.

http://msdn.microsoft.com/en-us/library/ms188365(v=sql.90).aspx

 

Just remember that the BULK INSERT command requires an extremely high level of permissions [ADMINISTER BULK OPERATIONS, a server-level permission], which you will be unable to grant in any application you build. You won’t notice this if you use sa, but sa is disabled on SQL2008 ;)

However, if you are doing an import from .NET code then the System.Data.SqlClient.SqlBulkCopy class is great, and needs far fewer permissions. If only they would give us the equivalent of SqlBulkCopy for exports too …

Sample Data:

D:\FileStore\names.csv

Title,Forename,Surname
Mr,John,Smith
Mrs,Jane,Smith
Miss,Elizabeth,Smith
Mr,James,Smith
Mr,Mathew,Smith
Mrs,Catherine,Smith
Miss,Mary,Smith
Mr,David,Smith

 

My Query :

CREATE TABLE [#FakeNames] (   
      Title VARCHAR(20),     
      Forename VARCHAR(50),
      Surname VARCHAR(50)
    )
BULK INSERT #FakeNames FROM 'D:\FileStore\names.csv' WITH(FIRSTROW = 2, MAXERRORS = 0, FIELDTERMINATOR = ',' )