insert multiple rows

  • Thread starter Thread starter Tem
  • Start date Start date
T

Tem

I have a string array tags with content as follows

string[] tags
cat
animal
pet


I need to insert all 3 rows with a single INSERT statement

INSERT INTO Tags (TagName, PhotoID) VALUES (@Tag, SCOPE_IDENTITY())

command.Parameters.Add("@Tag", SqlDbType.NVarChar).Value = tags


How can I do that?

Thanks

Tem
 
Tem,

This is the SQL script which is inserting a row in the SQL server.

In dotnet you can use this by using a SqlCommand.

There are plenty of these from which is the most easy one for this
SqlCommand.ExecuteNonQuerry

However have a look in the newsgroup

Microsoft.public.dotnet.framework.adonet

Where are people more specialized about your question.

Cor
 
I know it only inserts one row. I was hope that someone can help me change
it to insert multiple rows

Thanks

Cor Ligthert said:
Tem,

This is the SQL script which is inserting a row in the SQL server.

In dotnet you can use this by using a SqlCommand.

There are plenty of these from which is the most easy one for this
SqlCommand.ExecuteNonQuerry

However have a look in the newsgroup

Microsoft.public.dotnet.framework.adonet

Where are people more specialized about your question.

Cor


Tem said:
I have a string array tags with content as follows

string[] tags
cat
animal
pet


I need to insert all 3 rows with a single INSERT statement

INSERT INTO Tags (TagName, PhotoID) VALUES (@Tag, SCOPE_IDENTITY())

command.Parameters.Add("@Tag", SqlDbType.NVarChar).Value = tags


How can I do that?

Thanks

Tem
Click to expand...
Click to expand...
 
I have a string array tags with content as follows

string[] tags
cat
animal
pet

I need to insert all 3 rows with a single INSERT statement

INSERT INTO Tags (TagName, PhotoID) VALUES (@Tag, SCOPE_IDENTITY())

command.Parameters.Add("@Tag", SqlDbType.NVarChar).Value = tags

How can I do that?

Thanks

Tem
Click to expand...

Why not executing the same comand with different parameter three times?
 
There is no built in support in SQL Server to insert multiple rows directly.
With t-SQL, you have a few options:

1. Write your insert statement as INSERT.. SELECT ...UNION... SELECT..
UNION...
2. Generate a series of insert statements from your client programming
language and execute them on the server.
3. Fake an list or an array with your sets of values: You can do this in a
variety of ways, Check out the like Uri provided or see:
www.projectdmx.com/tsql/sqlarrays.aspx
 
Personally, for 3 rows I'd just call it 3 times; however, if you are
on SQL Server 2005 or above you can use xml very effectively to wrap
multiple logical entities into a single call.

If the 3 is actually 3000, then there are other options - for example,
using SqlBulkCopy to throw the data (at high speed)into a staging
table, and have an SP that moves the data into the regular table (I
don't recommend bulk-insertion into "live" tables).

Marc
 
Back
Top