Insert multiple rows from sql statement? RSS

• • Reply
  

  Rits4Friends

  Participant

  1784 Points

  305 Posts

  Re: Insert multiple rows from sql statement?

  Sep 30, 2009 08:34 AM|LINK

  look at this link:

  
  

  http://www.sqlhub.com/2009/09/array-or-generic-list-pass-to-sql.html
  

  Ritesh Shah
  http://www.SQLHub.com
  Fight the fear of SQL with SQLHub.com --Ritesh Shah
  
  
  
  
  
  
  
  Jainism
  All About Jainism
• • Reply
  

  nilsan

  All-Star

  16826 Points

  3684 Posts

  Re: Insert multiple rows from sql statement?

  Sep 30, 2009 09:18 AM|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')
  

  This is one of the best possible way.
  

  Blog | Get your forum question answered | Microsoft Community Contributor 2011
  
• • Reply
  

  nilsan

  All-Star

  16826 Points

  3684 Posts

  Re: Insert multiple rows from sql statement?

  Sep 30, 2009 09:23 AM|LINK

  RickNZ

  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.

  
  

  However you can use above mentioned thing, if your data is in table and you don't want to fire multiple insert queries to database.

  check it out here

  I hope this helps!! [:)]
  

  Blog | Get your forum question answered | Microsoft Community Contributor 2011
  
  • Marked as answer by Boddam on Sep 30, 2009 12:34 PM
• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: Insert multiple rows from sql statement?

  Sep 30, 2009 09:25 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..?
  

  -- suppose u want to insert record using @myValues
  -- than creat this function into your db
  
  
  CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
  returns @temptable TABLE (items varchar(8000))
  as
  begin
  	declare @idx int
  	declare @slice varchar(8000)
      
  
  	select @idx = 1
  		if len(@String)<1 or @String is null  return
      
  
  	while @idx!= 0
  	begin
  		set @idx = charindex(@Delimiter,@String)
  		if @idx!=0
  			set @slice = left(@String,@idx - 1)
  		else
  			set @slice = @String
  		
  
  		if(len(@slice)>0)
  			insert into @temptable(Items) values(@slice)
  
  
  		set @String = right(@String,len(@String) - @idx)
  		if len(@String) = 0 break
  	end
  return
  end
  
  
  -- after creating check below query
  
  
  Create Table #Table
  (
  	Id int identity(1,1),
  	ParamValue varchar(50) -- may other datatype in your case
  
  
  )
  
  
  insert into #Table
  Select items from dbo.Split(@myValues,',')
  -- where @myValues='1,2,3' value separted by comma
  
  
  Drop table #Table

  
  
  

  Yogesh Bhadauriya
  .Net All About
  
  • Edited by yrb.yogi on Sep 30, 2009 09:27 AM
• • Reply
  

  Niikola

  Contributor

  3996 Points

  761 Posts

  Re: Insert multiple rows from sql statement?

  Sep 30, 2009 12:59 PM|LINK

  yrb.yogi

  CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))       

  returns @temptable TABLE (items varchar(8000))       

  
  

  Just as a note, a you posted this version of split function that performs very bad.

  You can check other, much faster versions, here
  

  Smile...tomorrow will be worse
  • Edited by Niikola on Sep 30, 2009 01:00 PM
• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: Insert multiple rows from sql statement?

  Sep 30, 2009 01:18 PM|LINK

  Niikola

  yrb.yogi

  CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))       

  returns @temptable TABLE (items varchar(8000))       

  
  

  Just as a note, a you posted this version of split function that performs very bad.

  You can check other, much faster versions, here
  

  THNX Nikola...

  But my solution returns the whole table containing Id into single table

  while this one using 2 columns....

  
  

  check the output here...

  select * FROM dbo.SplitString('1$2$3','$')

  
  

  output

  ------------------

  Item Token

  -----  -----
  

  1      1

  2      2

  
  

  -----------------------

  and now to my split function

  
  

  select * FROM dbo.Split('1$2$3','$')

  
  

  output

  ------------

  items

  ------
  

  1
  2
  3

  
  

  Yogesh Bhadauriya
  .Net All About
  
  • Edited by yrb.yogi on Sep 30, 2009 01:24 PM
  • Edited by yrb.yogi on Sep 30, 2009 01:26 PM
• • Reply
  

  Niikola

  Contributor

  3996 Points

  761 Posts

  Re: Insert multiple rows from sql statement?

  Sep 30, 2009 01:29 PM|LINK

  I didn't updated this thread yet, but last Emtucifor's function perform's best (in my tests).

  Here is the slightly modified version I used for comparison of performance (works with varchar(8000) instead of original varchar(Max)).

  CREATE FUNCTION  [dbo].[Split](
      @List VARCHAR(8000),
      @Del VARCHAR(1)
  ) RETURNS TABLE
  AS
  RETURN (
      WITH Tokens AS (
          SELECT
              StartPos = 1,
              EndPos = CHARINDEX(@Del, @List COLLATE Latin1_General_BIN2 + @Del)
          UNION ALL
          SELECT
              EndPos + 1,
              CHARINDEX(@Del, @List COLLATE Latin1_General_BIN2 + @Del, EndPos + 1)
          FROM Tokens
          WHERE EndPos > 0
      )
      SELECT
          SUBSTRING(@List, StartPos, EndPos - StartPos) as value
      FROM Tokens
      WHERE EndPos <> 0
  )

  
  

  Smile...tomorrow will be worse
  • Edited by Niikola on Sep 30, 2009 01:50 PM
• • Reply
  

  Niikola

  Contributor

  3996 Points

  761 Posts

  Re: Insert multiple rows from sql statement?

  Sep 30, 2009 01:31 PM|LINK

  For SQL Server 2000 you will need one of fnSplit???_NUM versions from thread I posted above.
  

  Smile...tomorrow will be worse
• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: Insert multiple rows from sql statement?

  Sep 30, 2009 01:35 PM|LINK

  
  

  Niikola

  I didn't updated this thread yet, but last Emtucifor's function perform's best (in my tests).

  Here is the slightly modified version I used for comparison of performance (works with varchar(8000) instead of original varchar(Max)).

  
  

  <div class="dp-highlighter"><div class="bar"><div class="tools">view plaincopy to clipboardprint?
  </div></div>

  1. CREATE FUNCTION  [dbo].[Split](  
  2.     @List VARCHAR(8000),  
  3.     @Del VARCHAR(1)  
  4. ) RETURNS TABLE  
  5. AS  
  6. RETURN (  
  7.     WITH Tokens AS (  
  8.         SELECT  
  9.             StartPos = 1,  
  10.             EndPos = CHARINDEX(@Del, @List COLLATE Latin1_General_BIN2 + ',')  
  11.         UNION ALL  
  12.         SELECT  
  13.             EndPos + 1,  
  14.             CHARINDEX(@Del, @List COLLATE Latin1_General_BIN2 + ',', EndPos + 1)  
  15.         FROM Tokens  
  16.         WHERE EndPos > 0  
  17.     )  
  18.     SELECT  
  19.         SUBSTRING(@List, StartPos, EndPos - StartPos) as value  
  20.     FROM Tokens  
  21.     WHERE EndPos <> 0  
  22. )      

  </div>

  CREATE FUNCTION  [dbo].[Split](
      @List VARCHAR(8000),
      @Del VARCHAR(1)
  ) RETURNS TABLE
  AS
  RETURN (
      WITH Tokens AS (
          SELECT
              StartPos = 1,
              EndPos = CHARINDEX(@Del, @List COLLATE Latin1_General_BIN2 + ',')
          UNION ALL
          SELECT
              EndPos + 1,
              CHARINDEX(@Del, @List COLLATE Latin1_General_BIN2 + ',', EndPos + 1)
          FROM Tokens
          WHERE EndPos > 0
      )
      SELECT
          SUBSTRING(@List, StartPos, EndPos - StartPos) as value
      FROM Tokens
      WHERE EndPos <> 0
  )

  
  
  

  
  

  
  

  please check output of your Split function using below query..
  

  select * FROM dbo.Split('1$2$3','$')

  
  

  ---

  
  

  Yogesh Bhadauriya
  .Net All About
  
• • Reply
  

  Niikola

  Contributor

  3996 Points

  761 Posts

  Re: Insert multiple rows from sql statement?

  Sep 30, 2009 01:48 PM|LINK

  Thanks for pointing that out, it's a stupid mistake.

  Instead of concatenating delimeter to the string (value of @Del), literal value of comma was concatenated.

  I will update code

  Line 10:           

  EndPos = CHARINDEX(@Del, @List COLLATE Latin1_General_BIN2 + ',')  

  Should be

  EndPos = CHARINDEX(@Del, @List COLLATE Latin1_General_BIN2 + @del)  

  
  Line 14:

  CHARINDEX(@Del, @List COLLATE Latin1_General_BIN2 + ',', EndPos + 1) 

  Should be

  CHARINDEX(@Del, @List COLLATE Latin1_General_BIN2 + @del, EndPos + 1) 
  Smile...tomorrow will be worse