Loading CSV file Data in database using Bulk insert in sql server

I am getting daily CSV file from the client and my job is to load that data in sql server table. CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

Syntax of Bulk Insert

BULK INSERT [Database name]. [ schema name ][ table name ] FROM 'CSV file full path' [ WITH ( [ [ , ] BATCHSIZE =batch size ] [ [ , ] CHECK_CONSTRAINTS ] [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'codepage' } ] [ [ , ] DATAFILETYPE = { 'char' | 'native'| 'widechar' | 'widenative' } ] [ [ , ] FIELDTERMINATOR = 'field_terminator' ] [ [ , ] FIRSTROW = first row ] [ [ , ] FIRE_TRIGGERS ] [ [ , ] FORMATFILE ='format_file_path' ] [ [ , ] KEEPIDENTITY ] [ [ , ] KEEPNULLS ] [ [ , ] KILOBYTES_PER_BATCH =kilobytes_per_batch ] [ [ , ] LASTROW =last_row ] [ [ , ] MAXERRORS =max_errors ] [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] [ [ , ] ROWS_PER_BATCH =rows_per_batch ] [ [ , ] ROWTERMINATOR ='row_terminator' ] [ [ , ] TABLOCK ] [ [ , ] ERRORFILE ='file_name' ] )]

BATCHSIZE

We can define the number of rows in a batch. Each batch is copied to the server as one transaction. If this fails, SQL Server commits or rolls back the transaction for every batch. By default, all data in the data file is one batch.

CHECK CONSTRAINTS

All constraints on the target table must be checked during the bulk-import operation. Without this any check constraints are ignored.

CODEPAGE

It specifies the code page of the data in the data file.

FIELDTERMINATOR

 Here we specify the field terminator to be used for char and wide char data files. The default field terminator is \t (tab character). If the delimiters are (, : | ) here we need to define.

FIRSTROW

Suppose we want to skip some rows. Here we need to define.

FIRE_TRIGGERS

If we have insert trigger on the destination table and want to trigger after inserting the data during the bulk import operation. If we define FIRE_TRIGGERS it will fired for every completed batch. If we do not define the FIRE_TRIGGERS the after operation completed trigger not will trigger.

KEEPIDENTITY

Data files that contain identity values can be bulk imported into an instance of Microsoft SQL Server. By default, the values for the identity column in the data file that is imported are ignored and SQL Server assigns unique values automatically. The unique values are based on the seed and increment values that are specified during table creation.

KEEPNULLS

It specifies that empty columns should retain a null value during the bulk-import operation, instead of having any default values for the columns inserted.

KILOBYTES_PER_BATCH

It specifies the approximate number of kilobytes (KB) of data per batch as kilobytes_per_batch.

MAXERRORS

It specifies the maximum number of syntax errors allowed in the data before the bulk import operation is canceled. If it was not specified, the default is 10.

ROWTERMINATOR

Here we specify the row terminator to be used for char and wide char data files. The default row terminator is \r\n (newline character).

TABLOCK

It specifies that a table-level lock is acquired for the duration of the bulk-import operation.

ERRORFILE

 We can specify the file used to collect rows that have formatting errors and cannot be converted to an OLE DB row set. These rows are copied into this error file from the data file "as is." The error file is created when the command is executed. An error occurs if the file already exists. Additionally, a control file that has the extension .ERROR.txt is created. This references each row in the error file and provides error diagnostics. As soon as the errors have been corrected, the data can be loaded.

Let’s see the example

I am creating a table

create table product ( ProdictID varchar(10), Name varchar(50), ProductNumber varchar(50), SafetyStockLevel varchar(50), RecorderPoint varchar(50) )

I have a csv file

  

I want to load this file to the database.

See the syntax of Bulk insert above

BULK INSERT product FROM 'G:\Productcsv.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW =2 )

 

Data is loaded successfully in database. Now we see the result.