How to do a massive insert

Question

I have an application in which I have to insert in a database, with SQL Server 2008, in groups of N tuples and all the tuples have to be inserted to be a success insert, my question is how I insert these tuples and in the case that someone of this fail, I do a rollback to eliminate all the tuples than was inserted correctly.

Thanks

Answer 1

On SQL Server you might consider doing a bulk insert.

Answer 2

You should look into transactions. This is a good intro article that discusses rolling back and such.

Answer 3

For MySQL, look into LOAD DATA INFILE which lets you insert from a disk file.

Also see the general MySQL discussion on Speed of INSERT Statements.

For a more detailed answer please provide some hints as to the software stack you are using, and perhaps some source code.

Answer 4

From .NET, you can use SQLBulkCopy.

Table-valued parameters (TVPs) are a second route. In your insert statement, use WITH (TABLOCK) on the target table for minimal logging. eg:

INSERT Table1 WITH (TABLOCK) (Col1, Col2....)
SELECT Col1, Col1, .... FROM @tvp

Wrap it in a stored procedure that exposes @tvp as parameter, add some transaction handling, and call this procedure from your app.

You might even try passing the data as XML if it has a nested structure, and shredding it to tables on the database side.

Answer 5

If you are inserting the data directly from the program, it seems like what you need are transactions. You can start a transaction directly in a stored procedure or from a data adapter written in whatever language you are using (for instance, in C# you might be using ADO.NET).

Once all the data has been inserted, you can commit the transaction or do a rollback if there was an error.

See Scott Mitchell's "Managing Transactions in SQL Server Stored Procedures for some details on creating, committing, and rolling back transactions.

Answer 6

You have two competing interests, doing a large transaction (which will have poor performance, high risk of failure), or doing a rapid import (which is best not to do all in one transaction).

If you are adding rows to a table, then don't run in a transaction. You should be able to identify which rows are new and delete them should you not like how the look on the first round.

If the transaction is complicated (each row affects dozens of tables, etc) then run them in transactions in small batches.

If you absolutely have to run a huge data import in one transaction, consider doing it when the database is in single user mode and consider using the checkpoint keyword.