Bulk Insert

I am going to Bulk insert from my csv file to SQL Server table.

The CSV file format is

 

1,Rony Roy,1

2,Bioy Paul',2

3,Raja Husain,1

 

The CSV is easily open in the Notepad version and in my case I am putting it in C:\Test.casv

 

The Table objects that I created is

 

CREATE TABLE myTab

                           (SRLNO  decimal(1),

                           SNAME   VARCHAR(50),

                           CLASS    decimal(1))

 

The syntax for the BULK insert in mentioned bellow

 

BULK INSERT

   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]

      FROM 'data_file'

     [ WITH

        (

   [ [ , ] BATCHSIZE = batch_size ]

   [ [ , ] CHECK_CONSTRAINTS ]

   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]

   [ [ , ] 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' ]

        )]

 

Some important parameters are mentioned bellow:

 

Data_file  

 

it is the file from where we take the source of insert (may be the CSV file path)

 

BATCHSIZE = batch_size

 

Specified that the numbers of row in the batch were inserted, if the batch file fail/succeeded the entire transaction is going to ROLLBACK or COMMIT

 

CHECK_CONSTRAINTS

 

Specified that the constraints of the target table must be checked, it may be foreign key constraints or others constraints.

 

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }

 

Specifies the code page of the data in the data file. CODEPAGE is relevant only if the data contains char,varchar, or text columns with character values greater than 127 or less than 32.

 

CODEPAGE value	Description
ACP	Columns of char, varchar, or text data type are converted from the ANSI/Microsoft Windows code page (ISO 1252) to the SQL Server code page.
OEM(default)	Columns of char, varchar, or text data type are converted from the system OEM code page to the SQL Server code page.
RAW	No conversion from one code page to another occurs; this is the fastest option.
code_page	Specific code page number, for example, 850.

 

 

FIRSTROW = first_row

 

Specifies the number of the first row to load. The default is the first row in the specified data file.

 

FIRE_TRIGGERS

 

Specifies that any insert triggers defined on the destination table execute during the bulk-import operation. If triggers are defined for INSERT operations on the target table, they are fired for every completed batch.

If FIRE_TRIGGERS is not specified, no insert triggers execute.

 

FIELDTERMINATOR ='field_terminator'

 

Specifies the field terminator to be used for char and widechar data files. The default field terminator is \t (tab character). For more information

 

ROWTERMINATOR ='row_terminator'

 

Specifies the row terminator to be used for char and widechar data files. The default row terminator is \r\n(newline character).

 

Example:

 

BULK INSERT TargetTable

FROM 'c:\Test.csv'

WITH

(

   FIELDTERMINATOR=',',

   ROWTERMINATOR='\n'

)

 

 

For more details:

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

 

 

Posted By: MR. JOYDEEP DAS