Bulk Insert From Dataset

[Dianna]

I have a datafeed coming from another Database which I am populating into a
Dataset. I want to take the dataset and insert all the rows into a Sql
Table. So far I have an 'insert stored proc' and I pass all the parameters
from .Net and execute (1 row at a time). However, this is tedious. This
data feed contains about 100 columns (so I have to pass 100 parameters to the
Stored Proc). It is a straight insert (the columns in the datatable are the
same as the columns in the sql table and no identity column).

Is there an easier way to do this? In the old days I would have just used a
BCP. However, this app uses a Data Access Layer and we want to put all our
data access functions here.

I have been fooling around with the Data Adapter, but it seems that I will
have to still pass all the parameters to the insert stored proc.

Any help would be great.
Thanks, Dianna

[Shaun Ryan]

Hi Dianna...

If you moving large chunks of data around you really should use an ETL tool.

It would be very quick and simple to develop a DTS package to do a quick
bulk column copy (it is wise to disable transaction logging and remove and
rebuild indexes if the amount of is in the of order GBs).

Then write .NET code to call the DTS package passing in the source and
target connection properties for the database.

Shaun.

"Dianna" wrote:

I have a datafeed coming from another Database which I am populating into a
Dataset. I want to take the dataset and insert all the rows into a Sql
Table. So far I have an 'insert stored proc' and I pass all the parameters
from .Net and execute (1 row at a time). However, this is tedious. This
data feed contains about 100 columns (so I have to pass 100 parameters to the
Stored Proc). It is a straight insert (the columns in the datatable are the
same as the columns in the sql table and no identity column).

Is there an easier way to do this? In the old days I would have just used a
BCP. However, this app uses a Data Access Layer and we want to put all our
data access functions here.

I have been fooling around with the Data Adapter, but it seems that I will
have to still pass all the parameters to the insert stored proc.

Any help would be great.
Thanks, Dianna