469,946 Members | 1,805 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,946 developers. It's quick & easy.

Inserting multiple rows with a single INSERT query

Atli
5,058 Expert 4TB
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.
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO 
  2.   UserInfo(InfoName, InfoValue)
  3. VALUES
  4.   ('Name1', 'value1'),
  5.   ('Name2', 'value2')
  6.   /* And so on... */
  7.  
I tried this, by the way, and it did not work with MSSQL 2005.

I'd be gratefull for any suggestions.

-Atli
May 22 '07 #1
3 27930
Atli
5,058 Expert 4TB
The second I submitted the thread I figured this out :D

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [UserInfo] (InfoName, InfoValue)
  2.     SELECT 'Name1', 'Value1'
  3. UNION ALL 
  4.     SELECT 'Name2', 'Value2'
  5. UNION ALL 
  6.     SELECT 'Name3', 'Value3'
  7.  
Still, if you have a better idea I'd like to hear it.

-Atli
May 22 '07 #2
almaz
168 Expert 100+
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):
Expand|Select|Wrap|Line Numbers
  1. declare @UserInfo table (InfoName nvarchar(100), InfoValue  nvarchar(100))
  2.  
  3. declare @xml xml
  4. set @xml = '
  5. <root>
  6.     <user Name="User1" Value="Value1" />
  7.     <user Name="User2" Value="Value2" />
  8.     <user Name="User3" Value="Value3" />
  9.     <user Name="User4" Value="Value4" />
  10. </root>'
  11.  
  12. insert @UserInfo (InfoName, InfoValue)
  13. select UserData.value('@Name', 'nvarchar(100)'), UserData.value('@Value', 'nvarchar(100)')
  14. from @xml.nodes('/root/user') as Data(UserData) 
  15.  
  16. select * from @UserInfo
May 22 '07 #3
Atli
5,058 Expert 4TB
Nice one.
Makes me wander how much I don't know about MSSQL :P
May 22 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Raj Kotaru | last post: by
3 posts views Thread by gregory.sharrow | last post: by
5 posts views Thread by Arsen V. | last post: by
5 posts views Thread by Robert Brown | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.