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


Comments

Popular posts from this blog

ASP.NET Compillation Error BC31007 - Solution

The Difference between GET and POST

Test & Debug WCF service using WCFTestClient.exe