Re: Inserting Multiple Rows
From: Narayana Vyas Kondreddi (answer_me_at_hotmail.com)
Date: 08/24/04
- Next message: ChrisR: "revised attach files to new box"
- Previous message: Sam: "Capturing Stored Procedure, Table and View info with SQL Profiler"
- In reply to: Arsen V.: "Inserting Multiple Rows"
- Next in thread: Tibor Karaszi: "Re: Inserting Multiple Rows"
- Reply: Tibor Karaszi: "Re: Inserting Multiple Rows"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 24 Aug 2004 20:57:13 +0100
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.
-- Vyas, MVP (SQL Server) http://vyaskn.tripod.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 > >
- Next message: ChrisR: "revised attach files to new box"
- Previous message: Sam: "Capturing Stored Procedure, Table and View info with SQL Profiler"
- In reply to: Arsen V.: "Inserting Multiple Rows"
- Next in thread: Tibor Karaszi: "Re: Inserting Multiple Rows"
- Reply: Tibor Karaszi: "Re: Inserting Multiple Rows"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
