I needed to create a temp table in SQL with a column that contained an incrementing integer, without making that column an identity.
For example, suppose I want to select record from a table of users, but add a column that contains an incrementing counter. The data in the table should look like so:
To start, I can run the following query to create the temp table:
SELECT counter = 0, * FROM Users INTO #tmp_Users
This creates a temp table named #tmp_USers where the column "counter" is set to 0 for each row.
In order to update the counter column so that each row has a counter value greater than the previous row, run the following statement.
DECLARE @counter int
SET @counter = 0
SET @counter = counter = @counter + 1
If you’re looking to master your T-SQL Fu, I recommend this book. It provides a great reference for advanced T-SQL querying techniques. Keep in mind that this book is not for beginners. It gets into some deep analysis of how querying works in SQL 2005