The IDENTITY() function is used in a SELECT INTO statement to generate a result column with automatically generated values.
The IDENTITY() function works in the same way that the IDENTITY property generates them when you use it in a table.
SELECT
IDENTITY(<data_type> [, <seed>, <increment>]) AS column_name,
<other_columns>
INTO
<new_table_name>
FROM
<table_name>
WHERE
<search_criteria>
Query that Returns Result Row Numbers, Starting with 1 and Incremented by 1
22>
23> CREATE TABLE MyTable (
24> key_col int NOT NULL IDENTITY (1,1),
25> abc char(1) NOT NULL
26> )
27> INSERT INTO MyTable VALUES ('a')
28> INSERT INTO MyTable VALUES ('b')
29> INSERT INTO MyTable VALUES ('c')
30> SELECT * FROM MyTable ORDER BY key_col
31> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
key_col abc
----------- ---
1 a
2 b
3 c
(3 rows affected)
1>
2> SELECT
3> (SELECT
4> COUNT (*)
5> FROM
6> MyTable AS T2
7> WHERE
8> T2.abc <= T1.abc) AS rownum,
9> abc
10> FROM
11> MyTable AS T1
12> ORDER BY
13> abc
14>
15> drop table MyTable
16> GO
rownum abc
----------- ---
1 a
2 b
3 c
(3 rows affected)
1>