Adding an identity value with SELECT INTO

Category : Tips User Rating : 4 Stars      Views : 4 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.

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 :
INTO NewTable FROM ExistingTable
For a more specific example if we have a table called ExistingTable which contains two columns :

Existing Table
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

Existing Table
Link back to this article : an identity value with Select Into.aspx


SQL,Identity,Select Into


Post a comment   No login required !

Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered