Friday, November 7, 2014

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


No comments: