I came across a peculiar problem while working with table variables. Here is a simple code to demonstrate it:
declare @c tinyint
set @c = 1
while @c <= 2
declare @t table (srn int identity(1,1), name varchar(5))
insert into @t(name) select 'A'
insert into @t(name) select 'B'
select * from @t
set @c = @c + 1
The table variable does not get re-initialized and continues with the same identity seed for the next iteration.
I was wondering if anybody can throw some light on this behaviour of table variables where they do not get re-initialized with a declare statement. I worked around this problem by creating a temporary table and dropping at the end of each iteration nevertheless
I was wondering if any tweak can help with using the table variable.
View Complete Post