inserting multiple values in table at a time


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.

Comments

Popular posts from this blog

Rails Memcache issues

Enabling password authentication for new ec2 box | ssh, ssh config, EC2 setup, new user in EC2, PasswordAuthentication

What's the difference between "include" and "require" in Ruby?