Bulk Insert in ms sql server

Rakesh311 · Post by **Rakesh311** » Wed Mar 09, 2011 2:56 am

Hi

I want to insert more than 2 million of data to a SQL Server database.

The source is a flat file
Then need to do some sort of Transformation using a transformer.
Then need to load in to MS SQL database.

In datastage can anybody suggest me how to use bulk insert...please give me some detailed information.

:D

Thanks in advance
Rakesh

ray.wurlod · Post by **ray.wurlod** » Wed Mar 09, 2011 3:19 pm

Create load-ready text file using DataStage then invoke the bulk loader (bcp) under control of DataStage.

Rakesh311 · Post by **Rakesh311** » Thu Mar 10, 2011 12:02 am

Thanks Ray for your time...

I tried it with using bulk insert option of the RDBMS stage.....

I can see am improvement in the transaction, ie records are passing at a speed of 17000row/sec.

But for committing again its takes lot of time.
I kept commit size as 10000

Thanks
Rakesh

ray.wurlod · Post by **ray.wurlod** » Thu Mar 10, 2011 2:06 am

Commit is not relevant for bulk load. How have you set up the stage to communicate with the bulk loader?

Rakesh311 · Post by **Rakesh311** » Thu Mar 10, 2011 2:56 am

Hi,

In Dynamic RDBMS stage in General properties of Input, we have an option called Update Action

There we have Bulk Insert and also when we select this , It shows some some other option called as BULK with some properties

There it is asking for commit size and also mention it should be greater than 0.

Thanks,
Rakesh