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.
