SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Megan Beebe / insert multiple rows into variable / Latest Posts

RE: insert multiple rows into variable

daryl.scott — Fri, 18 May 2012 07:19:31 GMT

The reason the script is not revolutionary is because other like Erland Sommarskog performed a lot of testing to figure out the best way to do it ([url]http://www.sommarskog.se/arrays-in-sql-2005.html[/url]). In SQL Server 2005, a comma-delimited list is a great way to pass primary and foreign key values between stored procedures and between applications and the database.

RE: insert multiple rows into variable

Manie Verster — Fri, 18 May 2012 02:23:27 GMT

[quote][b]Boltz42 (5/17/2012)[/b][hr]I took one look at this article and thought "What's so special about this?" It's hardly revolutionary![/quote]I agree. I do not see the purpose for this. You would anyway need a loop to get the values again. :Whistling:

RE: insert multiple rows into variable

Boltz442 — Thu, 17 May 2012 18:45:53 GMT

I took one look at this article and thought "What's so special about this?" It's hardly revolutionary!

RE: insert multiple rows into variable

btaylor 78431 — Thu, 17 May 2012 07:39:39 GMT

Dude!This is a feature of TSQL we have utilized to great advantage for years.You are using set logic to implement the impact of each row upon an already existing variable.I have used it to generate median, average of average values and much more than simple string concatenation.For example, if you have a series of monthly averages and wish to get the annual average you can't just take the average of all 12.The formula is (period + 1) * (Previous Period + 1) -1 performed against all 12 periods. This works great using your select kind of statement by self joining the table against the previous row.Nice post.Cheers,Ben

RE: insert multiple rows into variable

daryl.scott — Thu, 17 May 2012 07:01:13 GMT

A comma-delimited list of values can also be generated using the for xml path approach.[code="sql"]select stuff((select ',' + cast(sNumber as varchar(10))from @demofor xml path('')),1,1,'');[/code]

RE: insert multiple rows into variable

Hardy21 — Wed, 16 May 2012 22:46:14 GMT

[quote][b]Mithrandir (7/21/2008)[/b][hr]Beware that this is an unsupported feature from MS.The results may be much different if you use a complex query to get the string. Happened to me once and it's a pain to debug.A workaround is to use a temp table to place the data with the complex query and get the results from there using a simple query. Anything to get rid of cursors![/quote]Agree with you.Also, without ORDER BY clause, it is not guaranteed about Order in which you receive result. Result may be different in good amount of rows.

RE: insert multiple rows into variable

Mithrandir — Mon, 21 Jul 2008 17:08:13 GMT

Beware that this is an unsupported feature from MS.The results may be much different if you use a complex query to get the string. Happened to me once and it's a pain to debug.A workaround is to use a temp table to place the data with the complex query and get the results from there using a simple query. Anything to get rid of cursors!

RE: insert multiple rows into variable

rossano di michele — Fri, 18 Jul 2008 01:35:36 GMT

the COALESCE function get the first NOT NULL value, so we don't need the line [code]select @Numbers = 0, @Strings = ''[/code]

RE: insert multiple rows into variable

Hari.Sharma — Thu, 17 Jul 2008 22:55:05 GMT

Yeah James.I just copied that line from earlier post and didnt changed it.But you are right.

RE: insert multiple rows into variable

Snookster — Thu, 17 Jul 2008 09:40:31 GMT

your COALESCE example results are ",ONE,TWO,THREE,FOUR,FIVE,SIX,SEVEN,EIGHT,NINE"to get "ONE,TWO,THREE,FOUR,FIVE,SIX,SEVEN,EIGHT,NINE" change the first line to...select @Numbers = 0, @Strings = NULL

RE: insert multiple rows into variable

Hari.Sharma — Thu, 17 Jul 2008 06:27:51 GMT

The Best way to use COALESCE function because its very fast compare to other techniques.select @Numbers = 0, @Strings = ''SELECT @Numbers = COALESCE(@Numbers,'')+iNumber,@Strings = COALESCE(@Strings+',','')+sNumberFROM @demo

RE: insert multiple rows into variable

licentiat — Thu, 17 Jul 2008 06:13:10 GMT

This will give you the string in the right format:--select all the rows into the variablesselect @Numbers = @Numbers + iNumber,@Strings=CASE @Strings WHEN '' THEN sNumber ELSE @Strings + ', '+sNumber ENDfrom @demoExcellent trick.Nick

insert multiple rows into variable

megan.beebe — Thu, 10 Jul 2008 09:50:12 GMT

Comments posted to this topic are about the item [B]insert multiple rows into variable[/B]