Table Variable and Temp Tables in Sql Server 2005

Here I am using Temp Table and Table Variable.
This code is using iteration logic (loop through).

--- tempLocations table which conists Id,LocationId ( 1,30 | 2,31 | 3,32 | 4,33 | 5,34 | 6, 35 )

Table variable:

declare table @TempLocations (Id int identity, LocationId int)

Temp Table :
declare table #TempGrid
(countOfCurrentPostalCode int, LocationId int, PostalCode varchar(20))

declare @loop int
set @loop = (select min(Id) from @TempLocations)
declare @LocationId int
WHILE @loop IS NOT NULL
BEGIN
select @LocationId = LocationId from @TempLocations where id = @loop
insert into #TempGrid
select top 200 count(PostalCode) as counter , locationid, postalcode from TempFinal where locationId = @LocationId group by locationid, postalcode order by counter desc
set @loop = (Select min(Id) from @TempLocations where Id > @loop )
END


select * from #TempGrid
select * from #tempLocations

1 comment:

Anonymous said...

I like it.

Thanks