Inserting Multiple Rows

From: Arsen V. (arsen_at_community.nospam)
Date: 08/24/04 

Date: Tue, 24 Aug 2004 14:48:33 -0500

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

Relevant Pages

  • Inserting Multiple Rows
    ... What is the optimal way to insert multiple rows from a web ... INSERT Table1 (field1) VALUES; ... Or is it better to execute each insert separatly. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Inserting Multiple Rows
    ... What is the optimal way to insert multiple rows from a web ... INSERT Table1 (field1) VALUES; ... Or is it better to execute each insert separatly. ...
    (microsoft.public.sqlserver.programming)
  • Derived RichTextBox containing objects?
    ... ArrayList as well as the Text & Rtf properties. ... Text: SELECT field1 FROM table1 ... FYI x.ToStringoutputs 'field1' and y.ToString ... but I wanted the Editor to be very easy to use. ...
    (microsoft.public.dotnet.framework.windowsforms.controls)
  • Re: DSUM HELP!!!
    ... Or even more efficient would be to use a subquery in the FROM clause of your query and join on Field1. ... FROM Table1 INNER JOIN ... GROUP BY Field1) as Temp ... You can only do the subquery in the FROM clause if your field and table names following the naming convention. ...
    (microsoft.public.access.gettingstarted)
  • RE: binding a combo box to with 2 lookup columns to two source col
    ... Control source: field1 (table1) ...
    (microsoft.public.access.formscoding)