CODEWITHSUNDEEP
sqlsql-serversql-server-2008insert
alfjos89
alfjos89

Reputation: 89

How to do a massive insert

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

Upvotes: 2

Views: 2488

Answers (6)

MatthewMartin
MatthewMartin

Reputation: 33143

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.

Upvotes: 0

Timothy Lee Russell
Timothy Lee Russell

Reputation: 3748

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.

Upvotes: 1

Peter Radocchia
Peter Radocchia

Reputation: 11007

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.

Upvotes: 2

patrickmdnet
patrickmdnet

Reputation: 3392

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.

Upvotes: 0

NG.
NG.

Reputation: 22904

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

Upvotes: 1

Justin Ethier
Justin Ethier

Reputation: 134167

On SQL Server you might consider doing a bulk insert.

Upvotes: 3

Related Questions