10

Using IDENTITY function with SELECT statement in SQL Server | SQL with Manoj

 3 years ago
source link: https://sqlwithmanoj.com/2011/12/15/using-identity-function-with-select-statement/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

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:
identity.jpg?w=595
-- Final Cleanup
DROP TABLE #tempTable

>> Check & Subscribe my [YouTube videos] on SQL Server.

Check the same demo here in YouTube:

Related


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK