How to generate autonumber (identity) value in existing table in single Update statement

Suppose you have some table and you want to populate
some column with AutoNumber (Identity) value.
For this task we can use
SET @variable = column = expression.
Consider the following example:

DECLARE @TmpTable TABLE
(
RowID INT NULL,
EmpName VARCHAR(10) NOT NULL
)
INSERT INTO @TmpTable(EmpName)
SELECT 'Dan' UNION ALL SELECT 'Greg' UNION ALL SELECT 'Jeck'
SELECT * FROM @TmpTable

The result of the Select statement will be:
---------------------------------------------
RowID EmpName
NULL Dan
NULL Greg
NULL Jeck

Now we declare @Count valiable and use it in our Update statement like this:

DECLARE @Count INT
SET @Count = 0
UPDATE @TmpTable
SET @Count = RowID = @Count +1

SELECT * FROM @TmpTable

The result of the Select statement will be:
---------------------------------------------
RowID EmpName
1 Dan
2 Greg
3 Jeck

What happening is that the @count variable gets every time
(every row) the value of the RowID column.

From the Books OnLine:
SET @variable = column = expression sets the variable to the same value as the column.
This differs from SET @variable = column, column = expression, which sets the variable
to the pre-update value of the column.

I think not all DBAs aware of this syntax(feature).

Tweet