BulkInsert Process

Bulkinsert process

Introduction

Usually if we want to Tranfer a text file into a DB table,There are two ways

1.Using SSIS

2.Using SQL

1.Using SSIS

We can use BulkInsert Task or Simple Dataflow task for this process.

2.Using SQL

We can use bcp,Openrowset or Bulkinsert

Scope of this document explains the use of Bulkinsert command.

Usually we prefer Bulkinsert because it is Faster and we can include many functions while Dataloading

The various steps included in the Dataloading are

1.Creation of Temperory or dummy table

2.Data loading to the Temperory table

3.Creation of actual table

4.Transfering data from Temperory table to Actual table

5.Droping of all temperory objects

1.Creation of Temperory or dummy table

This process includes the creation of Temperory table.Here temperory table means the actual physical table.It is not

advisable to use #table if we are loading Huge data.While using the Bulkinsert task we have to remove all Identity column

or default column if it is not available in the row data.And it is advisable to give large values(eg. varchar(1000)

for the first and last columns because there may be unusable vacant space at the end of each row.

2.Data loading to the Temperory table

Once temperory table is created we can load the data in to the Temperory table.We can have look on the Source data before

loading(If the data is more than 1 GB i recomend Jujuedit to view the content)

In normal case we use direct script to load the data

ie for eg.

———————————————

BULK INSERT TEST_TABLE

FROM ‘Test_data.txt’

WITH

(

FIELDTERMINATOR =’,’,

ROWTERMINATOR = ‘\n’

)

—————————————————

This is the script we use in Ideal situations like..If the row data dont have any header. or the row data generated

not by a unix machine where ROWTERMINATOR is not ‘\n’.

FIELDTERMINATOR is nothing but the character used to seperate each fields in the given examle its a comma.

ROWTERMINATOR is nothing but the entity which seperates each row in ideal case it will be newline character ‘\n’

we can enforce identity ,Unique constraint,primary key constraint or NOT NULL constraint by including KEEPIDENTITY

or CHECK_CONSTRAINT inside the bracket.

But we may not be lucky in all the cases.If the data is generated by a Unix based machine then the ideal query will

not work.So we have to use a special dynamic query

ie for eg.

———————————————————

declare @sql nvarchar(4000)

set @sql =

‘BULK INSERT Dest_table

FROM ”\\Loc ”

WITH

(

FIRSTROW = 2,

FIELDTERMINATOR = ”|”,

ROWTERMINATOR = ”’ + nchar(10) + ”’ ,

LASTROW=3355072

)’

select @sql

exec(@sql)

————————————————————–

Here FIRSTROW is specified because it will skip the header from the rowdata(Otherwise it will show error)

In certain cases LASTROW may be included if the last row of rawdata damaged.we can add IDENTITY or other constraint

like Default can be added to the table

eg scripts

——Adding Identity—————————

ALTER TABLE tbl

ADD tbl_ID bigint IDENTITY(1,1) NOT NULl

——Adding Defaults———————-

ALTER TABLE tbl

ADD RS_STATUS CHAR(1)

CONSTRAINT DF_tbl_Status DEFAULT ‘Y’ NOT NULL

ALTER TABLE tbl

ADD RS_PROCESS_DATE DATETIME

CONSTRAINT DF_CDR_PROCESS_DATE DEFAULT GETDATE() NOT NULL

——-Adding Primary Key—————-

ALTER TABLE tbl

ADD CONSTRAINT CUS_KEY PRIMARY KEY(tbl_ID)

After this process we will get the complete table.But we want to arrange the columns and column space

3.Creation of actual table

Creation of actual table include the process of creating the required table.

the columns should be arranged in the required order and column space should be managed.

4.Transfering data from Temperory table to Actual table

Now we can transfer data from the Dummy table to actual table.

eg.

INSERT INTO

(

COL1,COL2…….

)

SELECT

COL1,COL2…….

FROM DUMMY_TABLE

5.Droping of all temperory objects

After tranfering all data to the actual table we can drop the Dummy table

Bulkinsert process
Introduction
Usually if we want to Tranfer a text file into a DB table,There are two ways
1.Using SSIS2.Using SQL
1.Using SSIS
We can use BulkInsert Task or Simple Dataflow task for this process.
2.Using SQL
We can use bcp,Openrowset or Bulkinsert
Scope of this document explains the use of Bulkinsert command.
Usually we prefer Bulkinsert because it is Faster and we can include many functions while Dataloading
The various steps included in the Dataloading are
1.Creation of Temperory or dummy table
2.Data loading to the Temperory table
3.Creation of actual table
4.Transfering data from Temperory table to Actual table
5.Droping of all temperory objects

1.Creation of Temperory or dummy table
This process includes the creation of Temperory table.Here temperory table means the actual physical table.It is not advisable to use #table if we are loading Huge data.While using the Bulkinsert task we have to remove all Identity columnor default column if it is not available in the row data.And it is advisable to give large values(eg. varchar(1000)  for the first and last columns because there may be unusable vacant space at the end of each row.
2.Data loading to the Temperory table
Once temperory table is created we can load the data in to the Temperory table.We can have look on the Source data beforeloading(If the data is more than 1 GB i recomend Jujuedit to view the content)In normal case we use direct script to load the data
ie for eg.———————————————
BULK INSERT TEST_TABLEFROM ‘Test_data.txt’WITH(FIELDTERMINATOR =’,’,ROWTERMINATOR = ‘\n’)—————————————————
This is the script we use in Ideal situations like..If the row data dont have any header. or the row data generated not by a unix machine where ROWTERMINATOR is not ‘\n’.FIELDTERMINATOR is nothing but the character used to seperate each fields in the given examle its a comma.ROWTERMINATOR is nothing but the entity which seperates each row in ideal case it will be newline character ‘\n’we can enforce identity ,Unique constraint,primary key constraint or NOT NULL constraint by including KEEPIDENTITYor CHECK_CONSTRAINT inside the bracket.
But we may not be lucky in all the cases.If the data is generated by a Unix based machine then the ideal query willnot work.So we have to use a special dynamic query
ie for eg.———————————————————declare @sql nvarchar(4000)set @sql =‘BULK INSERT Dest_tableFROM ”\\Loc ”WITH(FIRSTROW = 2,FIELDTERMINATOR = ”|”,ROWTERMINATOR = ”’ + nchar(10) + ”’ ,LASTROW=3355072)’select @sqlexec(@sql)————————————————————–
Here FIRSTROW is specified because it will skip the header from the rowdata(Otherwise it will show error)In certain cases LASTROW may be included if the last row of rawdata damaged.we can add IDENTITY or other constraintlike Default can be added to the table
eg scripts ——Adding Identity—————————ALTER TABLE tblADD tbl_ID bigint IDENTITY(1,1) NOT NULl
——Adding Defaults———————-ALTER TABLE tblADD RS_STATUS CHAR(1)CONSTRAINT DF_tbl_Status DEFAULT ‘Y’ NOT NULL
ALTER TABLE tblADD RS_PROCESS_DATE DATETIMECONSTRAINT DF_CDR_PROCESS_DATE DEFAULT GETDATE() NOT NULL
——-Adding Primary Key—————-ALTER TABLE tblADD CONSTRAINT CUS_KEY PRIMARY KEY(tbl_ID)
After this process we will get the complete table.But we want to arrange the columns and column space
3.Creation of actual table
Creation of actual table include the process of creating the required table.the columns should be arranged in the required order and column space should be managed.
4.Transfering data from Temperory table to Actual table
Now we can transfer data from the Dummy table to actual table.
eg.
INSERT INTO (COL1,COL2…….)SELECTCOL1,COL2…….FROM DUMMY_TABLE
5.Droping of all temperory objects
After tranfering all data to the actual table we can drop the Dummy table

Advertisement