When copying data into a new table using SELECT INTO it can be useful to add an identity column
at the same time, especially where the source data does not already have a primary key.
To do this we can just define an identity column in the select into statement.
At its simplest this could be a statement like :
SELECT IDENTITY(INT,1,1) AS ID,*
INTO NewTable FROM
For a more specific example if we have a table called ExistingTable which contains two columns :
If we run the following code it will create a table with the data shown below :
SELECT IDENTITY(INT,1,1) AS ID, FirstName,Surname
INTO NewTable FROM ExistingTable