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 ( [at] Tag, SCOPE_IDENTITY())

command.Parameters.Add(" [at] 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


"Tem" <tem1232 [at] yahoo.com> schreef in bericht
news:%23oeDWA9YIHA.2000 [at] TK2MSFTNGP05.phx.gbl...
>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 ( [at] Tag, SCOPE_IDENTITY())
>
> command.Parameters.Add(" [at] Tag", SqlDbType.NVarChar).Value = tags
>
>
> How can I do that?
>
> Thanks
>
> Tem

Tem
SQL Server does NOT support arrays. See Erland's great article
http://www.sommarskog.se/arrays-in-sql.html




"Tem" <tem1232 [at] yahoo.com> wrote in message
news:%23oeDWA9YIHA.2000 [at] TK2MSFTNGP05.phx.gbl...
>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 ( [at] Tag, SCOPE_IDENTITY())
>
> command.Parameters.Add(" [at] Tag", SqlDbType.NVarChar).Value = tags
>
>
> How can I do that?
>
> Thanks
>
> Tem

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

Thanks

"Cor Ligthert[MVP]" <notmyfirstname [at] planet.nl> wrote in message
news:u#P6xE9YIHA.5028 [at] TK2MSFTNGP04.phx.gbl...
> 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" <tem1232 [at] yahoo.com> schreef in bericht
> news:%23oeDWA9YIHA.2000 [at] TK2MSFTNGP05.phx.gbl...
>>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 ( [at] Tag, SCOPE_IDENTITY())
>>
>> command.Parameters.Add(" [at] Tag", SqlDbType.NVarChar).Value = tags
>>
>>
>> How can I do that?
>>
>> Thanks
>>
>> Tem
>

On Jan 31, 2:11=A0pm, "Tem" <tem1... [at] yahoo.com> wrote:
> 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 ( [at] Tag, SCOPE_IDENTITY())
>
> command.Parameters.Add(" [at] Tag", SqlDbType.NVarChar).Value =3D tags
>
> How can I do that?
>
> Thanks
>
> Tem

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

--
Anith

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