August 23, 2010 at 8:09 pm
I have been reading a lot of the tally table articles by Jeff Moden and I have been very impressed with all the reviews and favorable results developers have been getting from it. My question is, can you apply the tally table to the following Loop for example (i think i read somewhere that it can replace almost all loops).
Here is the ddl and question
--DDL
if object_id('dbo.test') is not null
drop table dbo.test
go
create table test
(
id int IDENTITY(1,1),
levels int,
name varchar(50)
)
insert into test (levels, name)
select 1, 'apple' union all
select 1, 'apple' union all
select 1, 'apple' union all
select 2, 'orange' union all
select 2, 'orange' union all
select 2, 'orange' union all
select 2, 'orange' union all
select 3, 'banana' union all
select 3, 'banana' union all
select 3, 'banana' union all
select 3, 'banana' union all
select 3, 'banana'
--LOOP TO BE REPLACED WITH TALLY TABLE
DECLARE @id int
DECLARE @levels int
DECLARE @name varchar(20)
declare @rowcnt int
declare @maxrows int
set @rowcnt = 1
CREATE TABLE #temp
(
RowNum int IDENTITY (1,1) Primary key,
id int,
levels int,
name varchar(50)
)
insert into #temp
select id, levels, name
from test with(nolock)
select @MaxRows=count(*) from #temp
print @maxRows
WHILE exists (Select Rownum, id, levels, name
from #temp
where Rownum <= @MaxRows
and RowNum = @RowCnt
)
BEGIN
select @id =id, @levels = levels, @name = name
from #temp
where rownum <=@maxrows
and rownum = @rowcnt
print @id
/*
DO SOME SQL LOGIC
*/
select @rowcnt = @rowcnt + 1
END
ps: This loop replaced a cursor, how can the tally table replace this loop. Thx in advance
August 24, 2010 at 5:58 am
Could you please specify what is required output of your loop?
And why your input table contains full duplicates?
August 24, 2010 at 6:19 am
the devil is in the details, so "it depends"
this:
/*
DO SOME SQL LOGIC
*/
depedning on what that is doing, or going to do, you might not need a loop or tally table to extend your logic at all;
it might be a simple SET based update statement.
Tally tables are awesome, but the #1 rule is remove cursors/loops whenever possible with set based code. Replacing one loop with another kind of loop doesn't accomplish much.
Lowell
August 24, 2010 at 9:44 am
Thanks for the responses
Mr and Mrs. 500, the data in there is just something I made up, the duplicates do not matter (or does it?)
Lowell, the logic is to update each row and change the values to anything else (im making everything up).
Do these answers help any?
August 24, 2010 at 10:09 am
Well, I suggest that we take a step back, and start over.
You've already provided the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables, and that goes a long way in getting people to look at your issue and help you out. However, can you add what your expected results should be, based on the sample data provided? As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature. Of special note is the "do some sql logic". So, some sample data that shows the issue, and what the expected results are should give us enough to go further on this.
Thanks!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply