September 26, 2009 at 1:06 pm
Thanks for the feedback, Greg.
About 95% of what I do with Tally tables need a Tally table that starts with 1. That's why I haven't started mine at 0. I just do a t.N-1 if I need one that starts at zero. Of course, folks could start a Tally table at 0 and just remember that they have to say AND t.N > 0. Either way would work fine. Starting one at zero would probably be more effecient than using t.N-1.
I'll have to see if I can get a copy of that book because I don't understand why anyone would call it a "Pivot". When most folks think of a "Pivot", they think of something more along the lines of a cross-tab.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 26, 2009 at 1:17 pm
Heh... ok. I just did a preview of the book on Amazon. They called it a "Pivot" table because of it's use in splitting data from a single "horizontal list" of variables to a verticle list of values. If that were the only use for the table, I guess I'd call it that, too.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 28, 2009 at 6:43 am
Jeff,
here is a Google link to a copy of this book:
Have a good day
Greg
September 28, 2009 at 8:25 am
Thanks Greg.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 8, 2010 at 4:41 am
Great article! An eye-opener in every possible way ... even in the 'not recommended loops' code snippets I found stuff I had never seen before.
Cheers
Michael
February 10, 2010 at 6:31 am
Jeff Moden (5/4/2009)
JohnG (5/4/2009)
peter (5/4/2009)
True, from an ADO client application. However, It doesn't work from T-SQL. I.e., You cannot consume a result set returned by a stored procedure within T-SQL except through a CURSOR output parameter. That is why Microsoft has been providing two different flavors of some of the system stored procedures.Sure you can... OPENQUERY. OPENROWSET. Etc. Do those routines have an implit cursor? I'm sure they do... so does a SELECT. 🙂
This started from the point that the hosepipe returning multiple result sets wasn't ansi compliant because the wrong end controls the stream. I wasn't sure whether your comment was referring to multiple result sets, so I checked BOL (in case my memory was failing me or these features were upgraded and I didn't notice) but both for OPENROWSET and for OPENQUERY it is clearly stated that if the query returns multiple rowsets only the first is used (http://msdn.microsoft.com/en-gb/library/ms188427.aspx and http://msdn.microsoft.com/en-gb/library/ms190312.aspx).
There just isn't any way (that I've found) inside T-SQL of reading multiple rowsets returned by a stored procedure; multiple result sets can be passed from one SP through through a calling SP (which can't see those result sets) to the outside world via an ADO or ADO.NET connection, but they can't be gotten at internally in T-SQL. If someone knows a way it would be great to hear of it!
I have found this extremely frustrating over the years, since very many of my SPs return multiple rowsets, so their results can't be seen by other T-SQL code, and that's sometimes a pain. I wish MS would add this capability but I suspect they won't because too many people would react by complaining about yet another non-ANSI language extension.
Tom
February 10, 2010 at 7:00 am
True enough and even though I quoted what was said, I missed the "ADO" part because I tend to think in terms of "large batches" on the T-SQL side and my response concentrated only on the T-SQL. My apologies for that.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 10, 2010 at 7:11 am
m.ehrt (1/8/2010)
Great article! An eye-opener in every possible way ... even in the 'not recommended loops' code snippets I found stuff I had never seen before.Cheers
Michael
Sorry for the really late return on this post, Michael. I get a couple of hundred emails a day from this site and this one slipped through the cracks. Thanks for the awesome feedback and I'm glad the article helped.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 10, 2010 at 11:53 am
Tom.Thomson (2/10/2010)
...There just isn't any way (that I've found) inside T-SQL of reading multiple rowsets returned by a stored procedure; multiple result sets can be passed from one SP through through a calling SP (which can't see those result sets) to the outside world via an ADO or ADO.NET connection, but they can't be gotten at internally in T-SQL. If someone knows a way it would be great to hear of it!
Technically, this is not entirely correct. Although I am sure that this does not affect your point here, I need to clarify your statement, lest someone read this and come away with the wrong impression.
About 9-12 months ago while trying to help someone with a similar problem on the MSDN forums, I discovered that there is in fact a way to do this (albeit, with substantial restrictions, making it a special-case workaround, rather that a general solution). As it happens, the INSERT..EXEC technique can in fact consume any number of result sets into its target table, so long as one condition is satisfied. As long as every one of the returned result sets has "compatible" column definitions, INSERT..EXEC will do the equivalent of a UNION ALL of all of the result sets into the target table. "compatible" column sets here, apparently means, again, UNION-compatible.
Interesting sidelight, apparently, virtually no one knew this until I started pointing it out on MSDN and StackOverflow this past summer, as I spent half-a-day googling for this fact repeatedly. It was not noted in BOL, nor by anyone online, including every MS employee and MVP that I could think of. Heck, even Erland Sommarskog, who has a substantial article on the subject missed it and that guy catches *everything*, so I think anyone could be forgiven for not knowing this. (Another interesting sidelight, within 3 months of my posting this fact several times, numerous MVPs blogged about this very same "discovery". Oddly, not one of them gave me credit or even mentioned my name. Nice.)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 10, 2010 at 12:24 pm
RBarryYoung (2/10/2010)
About 8-10 months ago while trying to help someone with a similar problem on the MSDN forums, I discovered that there is in fact a way to do this (albeit, with substantial restrictions, making it a special-case workaround, rather that a general solution). As it happens, the INSERT..EXEC technique can in fact consume any number of result sets into its target table, so long as one condition is satisfied. As long as every one of the returned result sets has "compatible" column definitions, INSERT..EXEC will do the equivalent of a UNION ALL of all of the result sets into the target table. "compatible" column sets here, apparently means, again, UNION-compatible.
Well blow me down, I wasn't aware of that and I thought I knew this stuiff inside out :blush: Thanks very much for pointing this out, Barry.
It wouldn't have helped with the issues I had in the past as the multiple sets returned fromthose SPs were not union-compatible (nowhere near, in fact). But it's useful to know as I may in the future work on something with SPs that return multiple union-compatible sets.
[edited to remove redundant "something"]
Tom
February 10, 2010 at 1:05 pm
RBarryYoung (2/10/2010)
Tom.Thomson (2/10/2010)
...There just isn't any way (that I've found) inside T-SQL of reading multiple rowsets returned by a stored procedure; multiple result sets can be passed from one SP through through a calling SP (which can't see those result sets) to the outside world via an ADO or ADO.NET connection, but they can't be gotten at internally in T-SQL. If someone knows a way it would be great to hear of it!Technically, this is not entirely correct. Although I am sure that this does not affect your point here, I need to clarify your statement, lest someone read this and come away with the wrong impression.
About 9-12 months ago while trying to help someone with a similar problem on the MSDN forums, I discovered that there is in fact a way to do this (albeit, with substantial restrictions, making it a special-case workaround, rather that a general solution). As it happens, the INSERT..EXEC technique can in fact consume any number of result sets into its target table, so long as one condition is satisfied. As long as every one of the returned result sets has "compatible" column definitions, INSERT..EXEC will do the equivalent of a UNION ALL of all of the result sets into the target table. "compatible" column sets here, apparently means, again, UNION-compatible.
Interesting sidelight, apparently, virtually no one knew this until I started pointing it out on MSDN and StackOverflow this past summer, as I spent half-a-day googling for this fact repeatedly. It was not noted in BOL, nor by anyone online, including every MS employee and MVP that I could think of. Heck, even Erland Sommarskog, who has a substantial article on the subject missed it and that guy catches *everything*, so I think anyone could be forgiven for not knowing this. (Another interesting sidelight, within 3 months of my posting this fact several times, numerous MVPs blogged about this very same "discovery". Oddly, not one of them gave me credit or even mentioned my name. Nice.)
BWAA-HAAA!!!! Way cool, Barry! A "Quirky" Insert! I've got to give it a try.
What you mention is quite odd... considering the number of other MVP's that try to break my antlers for the "Quirky" update, you'd think they'd be afraid to publish anything about an undocumented feature such as this "Quirky" Insert you've discovered. We should at least hold them to the same "red letter" warning they all want me to put on my running total article.
You need to think of an acronym for your technique so I can start using it. You don't happen to have a link to your original post about it, do you, Barry?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 10, 2010 at 6:57 pm
Jeff Moden (2/10/2010)
What you mention is quite odd... considering the number of other MVP's that try to break my antlers for the "Quirky" update, you'd think they'd be afraid to publish anything about an undocumented feature such as this "Quirky" Insert you've discovered. We should at least hold them to the same "red letter" warning they all want me to put on my running total article.
Well, looks like I'm going to have to eat my words. As it turns out it is documented in the 2008 BOL for the INSERT Statement:
[font="Times New Roman"]execute_statement
Is any valid EXECUTE statement that returns data with SELECT or READTEXT statements.
If execute_statement is used with INSERT, each result set must be compatible with the columns in the table or in column_list.
execute_statement can be used to execute stored procedures on the same server or a remote server. The procedure in the remote server is executed, and the result sets are returned to the local server and loaded into the table in the local server. In a distributed transaction, execute_statement cannot be issued against a loopback linked server when the connection has multiple active result sets (MARS) enabled.
If execute_statement returns data with the READTEXT statement, each READTEXT statement can return a maximum of 1 MB (1024 KB) of data. execute_statement can also be used with extended procedures. execute_statement inserts the data returned by the main thread of the extended procedure; however, output from threads other than the main thread are not inserted.
You cannot specify a table-valued parameter as the target of an INSERT EXEC statement; however, it can be specified as a source in the INSERT EXEC string or stored-procedure. For more information, see Table-Valued Parameters (Database Engine).[/font]
The weird thing is that I did check the 2005 and 2000 doc: that line isn't there. But this feature isn't new to 2008, AFAIK, it's been around forever. Oh well, doesn't matter either way, but I sure can't go around whining that folks are stealing my ideas, when its right in the doc! :blush:
However, I am still the first person to mention it on the Internet, AFAIK. Here's four
of about ten references out there:
SQLServerCentral (mar 20, 09): http://www.sqlservercentral.com/Forums/Topic680741-338-1.aspx
This is the earliest example that I can find, but I'm not very obvious about it.
MSDN Forums (oct 23, 09): http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/f5bda101-1e80-49c6-bd66-c888d5ec8c40/
This one and the next one are the two that I think have been picked up by the most people.
Comment I left on Peso's Blog (Jun 28, 09) http://weblogs.sqlteam.com/peterl/archive/2009/06/27/Microsoft-Connect---Enhanced-Syntax-For-Insert-Into-Statement.aspx
Peso misunderstands what I am trying to say, but it's still there. Also notice that a "prakash" copies my comment...
A StackOverflow Answer that I posted (jun 12, 09) http://stackoverflow.com/questions/988192/grabbing-first-result-set-from-a-stored-proc-called-from-another-stored-proc/988234
(FYI: this is typical of what I hate about SO and their users: I tell him the answer but he refuses to accept it, and then refuses to give me credit for the correct answer. Grrrr...)
You need to think of an acronym for your technique so I can start using it.
Uh, OK. Anything's better than more of this "Quirky" business. 😀
Let's see, how about MIERS: Multiple Insert Execute Result Sets?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 11, 2010 at 6:48 am
Heh... so MS is only now discovering some of the features left behind by the people who originally wrote Sybase. 😛
Not to worry... if it's documented anywhere by MS, it ain't "Quirky" (or so it would seem).
--Jeff Moden
Change is inevitable... Change for the better is not.
March 19, 2010 at 1:20 am
Jeff Marcus (5/7/2008)
I am using the Tally table explanation from this article to parse out an incoming string that consists of whole records. By design, I know that the record has 3 values: an int, a datetime, and a float. What I need to get out are sets of those records parsed from the input string. The following SQL works fine (but I have questions):
declare @param varchar(8000)set @param = '101,2008.04.02,1.1,102,2008.04.03,2.2,103,2008.04.04,3.3'
declare @result table(idx int identity(1,1), val int, ord datetime, fval float)
declare @raw table (idx int identity(1,1), val varchar(50))
set @param = ',' + @param + ','
insert into @raw (val)
select substring(@param, N+1, charindex(',',@param,N+1)-N-1)
from dbo.Tally where N<len(@param) and substring(@param,N,1)=','
insert into @result(val,ord,fval)
select cast(R1.val as int), convert(datetime, R2.val, 102), convert(float, R3.val)
from dbo.Tally T
join @raw R1 on T.N=R1.idx
join @raw R2 on T.N+1=R2.idx
join @raw R3 on T.N+2=R3.idx
where (T.N-1)%3 = 0
select * from @result
As you can see I am parsing into a raw table that is varchar data with an index, then using that table to move sets of 3 items into a new table with the correct data type conversion.
My questions are:
1) Do I need to use the intermediate table or is there a way to do this going straight from the parameter string to the final table (while still being somewhat readable and understandable)?
2) If I remove the identity index from the @result table this stops working. The data conversions fail unless the @result table has the first column as an identity. All I do is insert the 3 converted items into @result, so I can't even begin to imagine why I need the index column. Does anyone else know why that happens?
Thanks for the extremely interesting and useful article, and for any assistance with these questions.
Jeff, I know this is old, but I've duplicated your Question 3 issue in SQL Server 2008. It looks as though SQL is trying to evaluate the "convert(datetime, R2.val, 102)" statement before it processes the "(T.N-1)%3 = 0" in the where clause. I don't understand why having @result.idx makes a difference (the query planner is obviously getting confused).
Adding the following join conditions makes the problem go away (note: our Tally table here includes 0 which simplifies the mod-3 math when using @raw.idx with identity(0,1)):
declare @param varchar(8000)
set @param = '101,2008.04.02,1.1,102,2008.04.03,2.2,103,2008.04.04,3.3'
--declare @result table(idx int identity(1,1), val int, ord datetime, fval float)
declare @result table(val int, ord datetime, fval float)
declare @raw table (idx int identity(0,1), val varchar(50))
set @param = ',' + @param + ','
insert into @raw (val)
select substring(@param, N+1, charindex(',',@param,N+1)-N-1)
from dbo.Tally
where N>0 and N<len(@param) and substring(@param,N,1)=','
insert @result (val, ord, fval)
select cast(R1.val as int)
, convert(datetime, R2.val, 102)
, convert(float, R3.val)
from dbo.Tally T
inner join @raw R1 on R1.idx=T.N and (R1.idx)%3 = 0
inner join @raw R2 on R2.idx=T.N+1 and (R2.idx)%3 = 1
inner join @raw R3 on R3.idx=T.N+2 and (R3.idx)%3 = 2
where (T.N)%3 = 0
select * from @result
July 29, 2010 at 12:00 am
Hi every1, i am extremely new to SQL.Can any1 show an example using a column instead of @parameter.
for the fetching of data between the commas.???(spliting the data).
email me plzz mfb1409@gmail.com
Viewing 15 posts - 286 through 300 (of 511 total)
You must be logged in to reply to this topic. Login to reply