October 4, 2007 at 6:09 am
I have the following problem. I need to insert 100.000 records (50Kb each) in one operation from a VB.net program into a SQL Server 2005 database.
All of these records will be ready for inserting at the same time.
How to make this insert as one big transaction instead of 100.000 small ones?
October 4, 2007 at 6:26 am
There are lots of ways. Using the bcp command line utility, you can bulk copy them in as a single batch. You could use SSIS (keeping in mind that the place the program runs needs to be an SSIS server). Also, you can use ADO to do this for you.
Going the ADO method, open a batch-updatable recordset (client-side will be fine) for a SELECT * on your table. With this type of recordset, you can call the Add Method to add a record and then set all of the column values for this record - then rinse and repeat for all of your records. When done, call the UpdateBatch method of the recordset and it will proceed to push all of the records in a single batch into your database (using a single transaction).
The down side of this will be that if there is a problem, everything rolls back.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply