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
"Tem"
news:%23oeDWA9YIHA.2000@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 (@Tag, SCOPE_IDENTITY())
>
> command.Parameters.Add("@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"
news:%23oeDWA9YIHA.2000@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 (@Tag, SCOPE_IDENTITY())
>
> command.Parameters.Add("@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]"
news:u#P6xE9YIHA.5028@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"
> news:%23oeDWA9YIHA.2000@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 (@Tag, SCOPE_IDENTITY())
>>
>> command.Parameters.Add("@Tag", SqlDbType.NVarChar).Value = tags
>>
>>
>> How can I do that?
>>
>> Thanks
>>
>> Tem
>
On Jan 31, 2:11=A0pm, "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 =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