Resetting an Incremental Update 1

[dukeslater]

I've inserted records into a temp table in the specific order they need to be sequenced:

visit sequence
100
100
100
200
200
300
400

What is the most efficient way to update the sequence so that it resets at each change of visit?

visit sequence
100 1
100 2
100 3
200 1
200 2
300 1
400 1


Thanks....

 

[vongrunt]

SQL Server 2000 or 2005?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[dukeslater]

Whoops... 2000.

 

[vongrunt]

Probably the simplest way is about ordered insert into temp table with identity. Method itself is handy but somewhat dirty... if everything works OK, fine.

Suppose this is source table:

create table srcData( visit int )
insert into srcData 
select 100 union all
select 100 union all
select 100 union all
select 200 union all
select 200 union all
select 300 union all
select 400

Ordered insert is done this way:

select identity(int, 1, 1) as globalseq, visit, convert(smallint, null) as sequence
into #tmp
from srcData
order by visit
option (maxdop 1) -- SMP = bad

select * from #tmp

Now based on global sequence it is easy to calculate sequence per visit:

update T
set sequence = globalseq - minseq + 1
from #tmp T
inner join
(	select visit, min(globalseq) as minseq
	from #tmp
	group by visit
) TG
on T.visit = TG.visit

select * from #tmp
order by visit, sequence

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[dukeslater]

Beautiful - thanks!!!