Re: Inserting Multiple Rows

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 08/24/04 

Date: Tue, 24 Aug 2004 21:54:38 +0200

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). 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Arsen V." <arsen@community.nospam> wrote in message news:%23RPgXNhiEHA.3172@tk2msftngp13.phx.gbl...
> 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

  • Re: Inserting Multiple Rows
    ... In addition to that [BCP, BULK INSERT, DTS], it might be worth mentioning below two things. ... Group several INSERT in the same transaction. ... Each batch requires a network roundtrip, ... mean what we see as "GO" in Query Analyzer and the method you use in ADO.NET to send the command to SQL Server ...
    (microsoft.public.sqlserver.programming)
  • Re: Inserting Multiple Rows
    ... In addition to that [BCP, BULK INSERT, DTS], it might be worth mentioning below two things. ... Group several INSERT in the same transaction. ... Each batch requires a network roundtrip, ... mean what we see as "GO" in Query Analyzer and the method you use in ADO.NET to send the command to SQL Server ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Efficient INSERT of rows- .NET
    ... In case Jon wants to stick with INSERT ... Group several INSERT in the same transaction. ... Each batch requires a network roundtrip, ... mean what we see as "GO" in Query Analyzer and the method you use in ADO.NET to send the command to SQL Server ...
    (microsoft.public.sqlserver.programming)
  • Re: Ignore SqlBulkCopy errors
    ... batch either committed or not. ... you need to use the bulk insert statement nested in a transaction. ... errors on duplicate key, I want ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Update time
    ... Because you are not within a transaction. ... SQL Server must prepare to rollback if any row fails. ... exponentially as you hit the hardware limitations of your computer. ... If *I* can figure out that updating a small batch> at a time is faster, why can't the fancy Query Optimizer figure out the> same thing, and do batching behind the scenes? ...
    (microsoft.public.sqlserver.programming)