The IDENTITY function can be defined for an identity column when the table is created and for a new column when the table is altered. Other than that, it can also be used in a SELECT INTO statement. Look at the below example.
Source Table
CREATE TABLE Test1(
column2 INT,
column3 VARCHAR(10))
INSERT INTO Test1 VALUES(10, ‘Greens’)
INSERT INTO Test1 VALUES(20, ‘Springs’)
INSERT INTO Test1 VALUES(30, ‘Meadows’)
INSERT INTO Test1 VALUES(10, ‘Greens’)
INSERT INTO Test1 VALUES(20, ‘Springs’)
SELECT * FROM Test1
There is no identify column in the table above. Now you may either change the table to add a new identity column or migrate the data to another table with an identity column created. But, we wanted to see how it can be included in a SELECT INTO query.
SELECT Column1= IDENTITY(INT, 1,1),
Column2,
Column3
INTO Test2
FROM Test1
GROUP BY Column2, Column3
SELECT * FROM Test2
Hope you find this post informative.
Please subscribe for more interesting updates.
Big Data and SQL 
2 comments