Insert multiple rows from sql statement? RSS

• • Reply
  

  Boddam

  Member

  347 Points

  335 Posts

  Insert multiple rows from sql statement?

  Sep 29, 2009 08:52 PM|LINK

  How can I insert multiple rows from a SQL statement?

  
  

  I know I can select multiple rows by "SELECT * FROM table WHERE id IN ('1','2','3')"

  Can I do the same with Insert? "INSERT INTO table (id) VALUES ('1','2','3')"

  
  

  I cant try on the computer im on right now so that's why I ask.
  

• • Reply
  

  whatispunk

  Contributor

  4074 Points

  876 Posts

  Re: Insert multiple rows from sql statement?

  Sep 29, 2009 09:09 PM|LINK

  INSERT INTO table (id)

  SELECT id FROM otherTable

  WHERE something = @someValue

  Remember: mark posts that helped you as the answer to aid future readers
  
  Why UpdatePanels Are Dangerous
  Why You Should Not Place Your Whole Site In An UpdatePanel
• • Reply
  

  watana72

  Participant

  1394 Points

  267 Posts

  Re: Insert multiple rows from sql statement?

  Sep 29, 2009 09:10 PM|LINK

  INSERT INTO table1 (id,Value) select id, value from table2 where id in (1,2,3)
  

  <div style="overflow: hidden; position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px;" id="_mcePaste">doc.SetParameterValue(0, 11)</div>
  Cheers,
  Nitin.
• • Reply
  

  Naom

  All-Star

  36004 Points

  7901 Posts

  Re: Insert multiple rows from sql statement?

  Sep 29, 2009 09:38 PM|LINK

  insert into table (ID) select ID from Table where ID in ('1','2','3')
  

  Beware of bugs in the above code; I have only proved it correct, not tried it.
  (Donald Knuth)
  
  Visit my blog
  
  Microsoft Community Contributor 2011-12
• • Reply
  

  Boddam

  Member

  347 Points

  335 Posts

  Re: Insert multiple rows from sql statement?

  Sep 29, 2009 09:44 PM|LINK

  But im not copying from another table, I mean litterly insert the new values 1,2,3 in a table that has one column (id) so when done I have a table with 3 rows.

  Something that is equivalent to this having an insert() fired after each one.
  

  INSERT INTO table (id) VALUES ('1')

  INSERT INTO table (id) VALUES ('2')

  INSERT INTO table (id) VALUES ('3')

• • Reply
  

  Naom

  All-Star

  36004 Points

  7901 Posts

  Re: Insert multiple rows from sql statement?

  Sep 29, 2009 09:55 PM|LINK

  SQL Server 2005 and down

  insert into table (ID) select '1' union select '2' union select '3'

  SQL Server 2008

  insert into table (ID) values ('1'), values ('2'), values ('3')
  

  Beware of bugs in the above code; I have only proved it correct, not tried it.
  (Donald Knuth)
  
  Visit my blog
  
  Microsoft Community Contributor 2011-12
  • Marked as answer by Boddam on Sep 30, 2009 12:32 PM
• • Reply
  

  Boddam

  Member

  347 Points

  335 Posts

  Re: Insert multiple rows from sql statement?

  Sep 29, 2009 10:24 PM|LINK

  Naom

  SQL Server 2005 and down

  insert into table (ID) select '1' union select '2' union select '3'

  SQL Server 2008

  insert into table (ID) values ('1'), values ('2'), values ('3')
  

  
  

  cool, thanks! What if I want to use a parameter, like "insert into table (ID) values @myValues". is that possible or waste of time trying..?
  

• • Reply
  

  Naom

  All-Star

  36004 Points

  7901 Posts

  Re: Insert multiple rows from sql statement?

  Sep 29, 2009 11:09 PM|LINK

   If you want to pass parameter like "1" or "1,2,3" then using any of the split functions http://forum.lessthandot.com/viewtopic.php?f=17&t=7566 available try

   

  insert into Table (ID) select F.Value from dbo.fn_Split(@myValues) F

  Beware of bugs in the above code; I have only proved it correct, not tried it.
  (Donald Knuth)
  
  Visit my blog
  
  Microsoft Community Contributor 2011-12
• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: Insert multiple rows from sql statement?

  Sep 30, 2009 06:58 AM|LINK

  Boddam

  Naom

  SQL Server 2005 and down

  insert into table (ID) select '1' union select '2' union select '3'

  SQL Server 2008

  insert into table (ID) values ('1'), values ('2'), values ('3')
  

  
  

  cool, thanks! What if I want to use a parameter, like "insert into table (ID) values @myValues". is that possible or waste of time trying..?
  

  
  

  better look at here http://forums.asp.net/p/1475397/3430735.aspx#3430735
  

  Yogesh Bhadauriya
  .Net All About
  
• • Reply
  

  RickNZ

  Contributor

  5233 Points

  880 Posts

  Re: Insert multiple rows from sql statement?

  Sep 30, 2009 08:27 AM|LINK

  Boddam

  What if I want to use a parameter, like "insert into table (ID) values @myValues". is that possible or waste of time trying..?
  

  In SQL 2008, you can use Table Valued Parameters.

  
  

  Check out my book:
  Ultra-Fast ASP.NET: Build Ultra-Fast and Ultra-Scalable web sites using ASP.NET and SQL Server
  My blog: http://www.12knowmore.com