Generating Identity Column in SELECT

Generating Identity Column in SELECT statements at runtime

There are situations where we need to generate an identity column [run time] seeding 1 when inserting records into staging or another tables. For example, you have a table where you have a primary key and identity has not been set on this primary key column.

If you want to insert records into this table on daily basis you need to generate the value for identity column when you are trying to insert records using select statements.

There are two easy ways to generate an primary identity column under select statements:

1. If you are inserting values using INTO statement you can use IDENTITY(INT,1,1) function in your select statement to generate unique values as shown below:

SELECT
 IDENTITY(INT, 1,1) AS EmployeeID,
 VALUE INTO #Ranks
FROM YourTable
WHERE 1=0

INSERT INTO #Ranks SELECT SomeColumn FROM YourTable ORDER BY SomeColumn

SELECT * FROM #Ranks Order By Ranks

The EmployeeID will be generated with unique identity number.

2. Another way of doing this is using; ROW_NUMBER() function,

SELECT ROW_NUMBER() OVER (ORDER BY colA) AS Row, cola
                FROM table1
This will also generates column with unique value seeded by 1.

Hope this helps!