TSQL - Auto ID for Temporary table variable
Today, I was trying optimize the query. For SQL Server temporary table variables, we cannot reseed for Identity ID. So I followed below approach: This process is used while you are trying to use the same temp table variable in while loop. Delete all the records from the table. Then while Inserting you can use Row_Number() to freshly start with new IDs from 1,2,3, etc. DECLARE @index int SET @index =0 DECLARE @TAB TABLE (ID INT ,VALUE VARCHAR(100)) INSERT INTO @Tab SELECT TOP 2 ROW_NUMBER() OVER (ORDER BY name) AS Row ,EmployeeName FROM dbo.Employee llb --ORDER BY llb.LoBName SELECT * FROM @Tab DELETE FROM @tab -- deleted two reords INSERT INTO @Tab SELECT TOP 4 ROW_NUMBER() OVER (ORDER BY name DESC ) AS ID ,EmployeeName FROM dbo.Employee llb --ORDER BY llb.LoBName desc SELECT * FROM @Tab -- now we got new 4 records starting from ID : 1,2,3,4