Using IDENTITY function with SELECT statement in SQL Server
In MS BOL for IDENTITY columns it is mentioned that [link]: “It creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.”
But experimenting with IDENTITY function I learnt this new thing with is not mentioned (or I could not find) anywhere in MS BOL that: “IDENTITY function can also be used with a SELECT statement but with some condition.”
Let’s check what’s that condition is:
USE [AdventureWorks] GO -- Let's use Person.Contact table in our SELECT statement and add a new column as IDENTITY function to it: SELECT IDENTITY (INT, 100, 5) AS NEW_ID, ContactID, Title, FirstName, MiddleName, LastName, EmailAddress, Phone FROM Person.Contact
Error Msg thrown:
Msg 177, Level 15, State 1, Line 1
The IDENTITY function can only be used when the SELECT statement has an INTO clause.
As per the error msg we cannot add an IDENTITY column to a SELECT query. The SELECT should be followed by an INTO clause. This way a new table will be created and records will be entered with the new IDENTITY column.
-- Let's add INTO clause in SELECT statement: SELECT IDENTITY (INT, 100, 5) AS NEW_ID, ContactID, Title, FirstName, MiddleName, LastName, EmailAddress, Phone INTO #tempTable FROM Person.Contact
Again an Error Msg:
Msg 8108, Level 16, State 1, Line 1
Cannot add identity column, using the SELECT INTO statement, to table ‘#tempTable’, which already has column ‘ContactID’ that inherits the identity property.
Completely agree with this!!! You cannot have 2 IDENTITY columns in a single table.
-- So we will remove the ContactID column as we want to have new ID column. SELECT IDENTITY (INT, 100, 5) AS NEW_ID, Title, FirstName, MiddleName, LastName, EmailAddress, Phone INTO #tempTable FROM Person.Contact -- This worked perfectly. -- Now let's check the newly created temp table and inserted records: select * from #tempTable -- Output below shows selected records from Person.Contact table with a new column having values starting from 100 with a gap of 5:
-- Final Cleanup DROP TABLE #tempTable