Adding an identity value with SELECT INTO

Category : Tips User Rating : 4.5 Stars      Views : 4.5 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 :
SELECT IDENTITY(INT,1,1) AS ID,*
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

Related Articles

The following articles may also be of interest :

Link back to this article : https://www.sqlmatters.com/Articles/Adding an identity value with Select Into.aspx

Keywords

SQL,Identity,Select Into


Comments
Post by david on Fri 07 Jun 2019 20:16. Report Inappropriate Post

This is great. How do you add values to empty table that table has a foreign key using: SELECT INTO? I am unclear how to use SCOPE clause, where to use in in the SQL statements. I understand the Primary Key is automatically entered.
I have:
INSERT INTO [Schema].[Table]([FOREIGNKey from different table], [DisplayName], [OfferType], [Price],[Createdate]) VALUES (????, 'ACME','RRKiller',99.99,GETDATE()) What should be in the ???? place in VALUES section

Thank you
Post by Ikram on Thu 04 Jul 2019 10:46. Report Inappropriate Post

Thanks! It helped like a charm.

Post a comment   No login required !

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