Inserting multiple rows with a single INSERT query

[Atli]

Hi.

I've been trying to insert multiple rows into a table using a single INSERT statement in MSSQL / SQL Server 2005.
I could of course cheat and have my C# code insert each row using some sort of a loop, but where is the fun in that :-)

If you'r familliar with MySQL, this MySQL query would do what I am trying to do with MSSQL / SQL Server 2005.

INSERT INTO 
  UserInfo(InfoName, InfoValue)
VALUES
  ('Name1', 'value1'),
  ('Name2', 'value2')
  /* And so on... */
 

I tried this, by the way, and it did not work with MSSQL 2005.

I'd be gratefull for any suggestions.

-Atli

[Atli]

The second I submitted the thread I figured this out :D

INSERT INTO [UserInfo] (InfoName, InfoValue)
    SELECT 'Name1', 'Value1'
UNION ALL 
    SELECT 'Name2', 'Value2'
UNION ALL 
    SELECT 'Name3', 'Value3'
 

Still, if you have a better idea I'd like to hear it.

-Atli

[almaz]

The second I submitted the thread I figured this out :D

Yes, this is one of possible solutions. Another one is to use XML (sample works on MSSQL 2005):

declare @UserInfo table (InfoName nvarchar(100), InfoValue  nvarchar(100))
 
declare @xml xml
set @xml = '
<root>
    <user Name="User1" Value="Value1" />
    <user Name="User2" Value="Value2" />
    <user Name="User3" Value="Value3" />
    <user Name="User4" Value="Value4" />
</root>'
 
insert @UserInfo (InfoName, InfoValue)
select UserData.value('@Name', 'nvarchar(100)'), UserData.value('@Value', 'nvarchar(100)')
from @xml.nodes('/root/user') as Data(UserData) 
 
select * from @UserInfo

[Atli]

Nice one.
Makes me wander how much I don't know about MSSQL :P