Inserting Multiple Rows

[Arsen V.]

Hello,

What is the optimal way to insert multiple rows (around 1000) from a web
application into a table?

The user enters multiple lines into a text box (up to 10,000). The ASP.NET
application breaks that data into a string array. Each line is an item of
that array.

The user clicks Submit.

I want to insert all those lines into a table in SQL Server.

I know that with MySQL 4.00 and newer, I can simply issue the following
command:

INSERT INTO Table1 (field1) VALUES ('apple'), ('pear'), ('soda'), ('drink')

This will very quickly insert all those values into Table1.field1.

I know that in SQL Server, I can use a BULK INSERT from a file or BCP.
However, I need to do the insert from a web application.

It is better to create one large SqlCommand with all the insert statements:

INSERT Table1 (field1) VALUES ('apple');
INSERT Table1 (field1) VALUES ('pear');
INSERT Table1 (field1) VALUES ('fruit');
INSERT Table1 (field1) VALUES ('drink');

and execute it in one shot.

Or is it better to execute each insert separatly.

Thanks,
Arsen

 

[Tibor Karaszi]

Arsen,

Here's a reply I just posted in -programming in the same topic:

In addition to that [BCP, BULK INSERT, DTS], it might be worth mentioning below two things. In case Jon wants
to stick with INSERT
statements:

1. Group several INSERT in the same transaction. Each transaction requires an I/O (write to the transaction
log). You can cut time, perhaps to some 10% by doing several in the same transaction. Not too many, though.
Start with about 1k - 5k and test from there.

2. Group several INSERT in the same batch. Each batch requires a network roundtrip, parsing etc. By batch, I
mean what we see as "GO" in Query Analyzer and the method you use in ADO.NET to send the command to SQL Server
(each ExecuteNonQuery is a batch, for instance).

 

[Narayana Vyas Kondreddi]

Making a call for each insert would be the slowest option.

In SQL Server, you could insert multiple rows as shown below:

INSERT INTO TableName (Col1, Col2)
SELECT 1, 2
UNION ALL
SELECT 3, 4
UNION ALL
SELECT 5, 6

Also see OPENXML in SQL Server 2000 Books Online.

 

[Tibor Karaszi]

Vyas,

Do you think that a SELECT with UNION will significally outperform several INSERTs in the same batch and
transaction? I never thought about comparing the two...

If it weren't for my girlfriend complaining right now in the living room, I'd do a test right now...

 

[Guest]

How important is loading all rows or no rows? Are all 1000 rows one logical
transaction? Is the application fault-tolerant and able to handle a "partial
load"?

Michael

 

[Toby Herring]

I concur with Narayana, I think that in your specific case, OPENXML is the
way to go for you.