MonsterRocks,
This can be done using a feature in SQL Server that will update a variable and a table in the same statement.
CREATE TABLE #TAB2 (SNO INT, UNQ_ID BIGINT)
INSERT INTO #TAB2 VALUES(1,10)
I'm assuming you don't really want to do this in a temp table since there's no reason to do it in a temp table.
DECLARE @SomeBigInt BIGINT
UPDATE #TAB2
SET
@SomeBigInt = UNQ_ID
, UNQ_ID = UNQ_ID + 1
You don't need an explicit transaction since it is done in a single statement, which is already in implicit transaction.
Todd Fifield