Fill MSSQL Table from DataSet 1

[mlager]

I've got an app where I want to pull data from a MySQL database into a DataSet, then take the data in the DataSet and insert its records into a SQL Server database table. What is the best way to accomplish this?

 

[Skittle]

I have a similar problm.

I have a dataset populated from an excel file that I want to write to an Iseries table. The only way I have been able to find to do this is to read through the dataset, one record at a time and issue a single INSERT SQL statement for each record. This means if my Excel file contains 10,000 rows, I perform 10,000 individual INSERT statements within a loop. This is a slow process.

There has got to be a better way to do this. Can anybody help us out?


Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[Skittle]

...I guess I am looking for:-

INSERT INTO MyConnection1.MyTable(Field1, Field2)
SELECT MyDatasetTableA(FieldA, FieldB)

Can this be done in .Net?



Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[mlager]

That's exactly what I ended up doing, and after I was finished, i thought to myself, this sucks! What a low tech solution. Like you said, there has to be a better way!

 

[RiverGuy]

mlager: .Net has BulkCopy classes for SQL Server. This will give you performance similar to SSIS when you have a SQL Server destination and choose FastLoad. Read about it here:

http://dotnetslackers.com/articles/ado_net/SqlBulkCopy_in_ADO_NET_2_0.aspx

Skittle, you might check to see if IBM has a similar set of classes to download. If not, also try declaring a transaction before your 10,000 insert statements, and committing afterward, to see if that speeds things up.

 

[mlager]

Thanks, I'll check this out!

 

[Skittle]

RiverGuy, thanks, two interesting leads.

I have just been trying an out of process AS400 data transfer option which is much quicker than loads of individual inserts but of course it relies on an external program running in a shell command.

Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.