Suppose you are asked to find out Identity column used in a table and get its values while you do not know table column names and which columns are having an identity.
Lets take an example to demonstrate scenario, First we create a table having identity column.
CREATE TABLE #T (SNO INT IDENTITY(1,1) , REMARKS VARCHAR(50)) INSERT INTO #T (REMARKS) VALUES ('Accepted')
then we insert one sample records into table.
SELECT * FROM #T
We have created a table #T having identity column – SNO .
Following statement returns identityย columns with values from a table without using column name in query.
SELECT $IDENTITY FROM #T
As we see, It returns identity column name with values even without using any column name in T-SQL Query.
$IDENTITY returns error if table does not have an identity column.
Now we create a table that does not have any identity column.
CREATE TABLE #T1 (SNO INT , REMARKS VARCHAR(50)) INSERT INTO #T1 (SNO,REMARKS) VALUES (1,'Accepted')
SELECT $IDENTITY FROM #T
As you can see, this time it returns an error as we do not have any identity column in our table.
1,344 total views, 1 views today
Thank you!
This solved a problem I had when retrieving the identity value using Output.
Now I can use INSERTED.$identity instead of the specific identity column name!
Example:
CREATE TABLE #RowsInserted (Id INT);
INSERT INTO MyTable (ColOne, ColTwo)
OUTPUT INSERTED.$identity INTO #RowsInserted
values (‘Val1′,’Val2’);
SELECT Id FROM #RowsInserted;