Get IDENTITY value in the same T-SQL statement it is created in?

Question

I was asked if you could have an insert statement, which had an ID field that was an "identity" column, and if the value that was assigned could also be inserted into another field in the same record, in the same insert statement.

Is this possible (SQL Server 2008r2)?

Thanks.

Answer 1

I know the original post was a long while ago. But, the top-most solution is using a trigger to update the field after the record has been inserted and I think there is a more efficient method.

Using a trigger for this has always bugged me. It always has seemed like there must be a better way. That trigger basically makes every insert perform 2 writes to the database, (1) the insert, and then (2) the update of the 2nd int. The trigger is also doing a join back into the table. This is a bit of overhead to have especially for a large database and large tables. And I suspect that as the table gets larger, the overhead of this approach does also. Maybe I'm wrong on that. But, it just doesn't seem like a good solution on a large table.

I wrote a function fn_GetIdent that can be used for this. It's funny how simple it is but really was some work to figure out. I stumbled onto this eventually. It turns out that calling IDENT_CURRENT(@variableTableName) from within a function that is called from the INSERT statements SET value assignment clause acts differently than if you call IDENT_CURRENT(@variableTableName) from the INSERT statement directly. And it makes it where you can get the new identity value for the record that you are inserting.

There is one caveat. When the identity is NULL (ie - an empty table with no records) it acts a little differently since the sys.identity_columns.last_value is NULL. So, you have to handle the very first record entered a little differently. I put code in the function to address that, and now it works.

This works because each call to the function, even within the same INSERT statement, is in it's own new "scope" within the function. (I believe that is the correct explanation). So, you can even insert multiple rows with one INSERT statement using this function. If you call IDENT_CURRENT(@variableTableName) from the INSERT statement directly, it will assign the same value for the newID in all rows. This is because the identity gets updated after the entire INSERT statement finishes processing (within the same scope). But, calling IDENT_CURRENT(@variableTableName) from within a function causes each insert to update the identity value with each row entered. But, it's all done in a function call from the INSERT statement itself. So, it's easy to implement once you have the function created.

This approach is a call to a function (from the INSERT statement) which does one read from the sys.identity_columns.last_value (to see if it is NULL and if a record exists) within the function and then calling IDENT_CURRENT(@variableTableName) and then returning out of the function to the INSERT statement to insert the row. So, it is one small read (for each row INSERTED) and then the one write of the insert which is less overhead than the trigger approach I think. The trigger approach could be rather inefficient if you use that for all tables in a large database with large tables. I haven't done any performance analysis on it compared to the trigger. But, I think this would be a lot more efficient, especially on large tables.

I've been testing it out and this seems to work in all cases. I would welcome feedback as to whether anyone finds where this doesn't work or if there is any problem with this approach. Can anyone can shoot holes in this approach? If so, please let me know. If not, could you vote it up? I think it is a better approach.

So, maybe being holed up due to COVID-19 out there, turned out to be productive for something. Thank you Microsoft for keeping me occupied. Anyone hiring? :) No, seriously, anyone hiring? OK, so now what am I going to do with myself now that I am done with this? :) Wishing everyone safe times out there.

Here is the code below. Wondering if this approach has any holes in it. Feedback welcomed.

IF OBJECT_ID('dbo.fn_GetIdent') IS NOT NULL 
  DROP FUNCTION dbo.fn_GetIdent; 
GO

CREATE FUNCTION dbo.fn_GetIdent(@inTableName AS VARCHAR(MAX))
RETURNS Int
WITH EXECUTE AS CALLER
AS
BEGIN

    DECLARE @tableHasIdentity AS Int
    DECLARE @tableIdentitySeedValue AS Int  

    /*Check if the tables identity column is null - a special case*/
    SELECT 
        @tableHasIdentity = CASE identity_columns.last_value WHEN NULL THEN 0 ELSE 1 END,
        @tableIdentitySeedValue = CONVERT(int, identity_columns.seed_value)
    FROM sys.tables
    INNER JOIN sys.identity_columns
    ON tables.object_id = identity_columns.object_id
    WHERE identity_columns.is_identity = 1
    AND tables.type = 'U'
    AND tables.name = @inTableName;


    DECLARE @ReturnValue AS Int;
    SET @ReturnValue = CASE @tableHasIdentity WHEN 0 THEN @tableIdentitySeedValue
                            ELSE IDENT_CURRENT(@inTableName) 
                            END;

    RETURN (@ReturnValue);

END

GO

/* The function above only has to be created the one time to be used in the example below */

DECLARE @TableHasRows AS Bit

DROP TABLE IF EXISTS TestTable

CREATE TABLE TestTable (ID INT IDENTITY(1,1), 
                        New INT, 
                        Letter VARCHAR (1))

INSERT INTO TestTable (New, Letter)
VALUES  (dbo.fn_GetIdent('TestTable'), 'H')

INSERT INTO TestTable (New, Letter)
VALUES  (dbo.fn_GetIdent('TestTable'), 'e')

INSERT INTO TestTable (New, Letter)
VALUES  (dbo.fn_GetIdent('TestTable'), 'l'),
        (dbo.fn_GetIdent('TestTable'), 'l'),
        (dbo.fn_GetIdent('TestTable'), 'o') 
               
INSERT INTO TestTable (New, Letter)
VALUES  (dbo.fn_GetIdent('TestTable'), ' '),
        (dbo.fn_GetIdent('TestTable'), 'W'),
        (dbo.fn_GetIdent('TestTable'), 'o'),
        (dbo.fn_GetIdent('TestTable'), 'r'),
        (dbo.fn_GetIdent('TestTable'), 'l'),
        (dbo.fn_GetIdent('TestTable'), 'd')


INSERT INTO TestTable (New, Letter)
VALUES  (dbo.fn_GetIdent('TestTable'), '!')

SELECT * FROM TestTable

/*

Result
ID  New Letter
1   1   H
2   2   e
3   3   l
4   4   l
5   5   o
6   6    
7   7   W
8   8   o
9   9   r
10  10  l
11  11  d
12  12  !

*/

Answer 2

use this simple code `SCOPE_IDENTITY()+1

Answer 3

About the cheked answer:

You cannot really do this - because the actual value that will be used for the IDENTITY column really only is fixed and set when the INSERT has completed.

marc_s I suppose, you are not actually right. Yes, He can! ))

The way to solution is IDENT_CURRENT():

CREATE TABLE TemporaryTable(
    Id int PRIMARY KEY IDENTITY(1,1) NOT NULL,
    FkId int NOT NULL 
)

ALTER TABLE TemporaryTable
    ADD CONSTRAINT [Fk_const] FOREIGN KEY (FkId) REFERENCES [TemporaryTable] ([Id])

INSERT INTO TemporaryTable (FkId) VALUES  (IDENT_CURRENT('[TemporaryTable]'))
INSERT INTO TemporaryTable (FkId) VALUES  (IDENT_CURRENT('[TemporaryTable]'))
INSERT INTO TemporaryTable (FkId) VALUES  (IDENT_CURRENT('[TemporaryTable]'))
INSERT INTO TemporaryTable (FkId) VALUES  (IDENT_CURRENT('[TemporaryTable]'))

UPDATE TemporaryTable 
   SET [FkId] = 3
 WHERE Id = 2

SELECT * FROM TemporaryTable

DROP TABLE TemporaryTable

More over, you can even use IDENT_CURRENT() as DEFAULT CONSTRAINT and it works instead of SCOPE_IDENTITY() for example. Try this:

CREATE TABLE TemporaryTable(
    Id int PRIMARY KEY IDENTITY(1,1) NOT NULL,
    FkId int NOT NULL DEFAULT IDENT_CURRENT('[TemporaryTable]')
)

ALTER TABLE TemporaryTable
    ADD CONSTRAINT [Fk_const] FOREIGN KEY (FkId) REFERENCES [TemporaryTable] ([Id])

INSERT INTO TemporaryTable (FkId) VALUES (DEFAULT)
INSERT INTO TemporaryTable (FkId) VALUES (DEFAULT)
INSERT INTO TemporaryTable (FkId) VALUES (DEFAULT)
INSERT INTO TemporaryTable (FkId) VALUES (DEFAULT)

UPDATE TemporaryTable 
   SET [FkId] = 3
 WHERE Id = 2

SELECT * FROM TemporaryTable

DROP TABLE TemporaryTable

Answer 4

You can do both.

To insert rows with a column "identity", you need to set identity_insert off.

Note that you still can't duplicate values!

You can see the command here. Be aware to set identity_insert on afterwards.

To create a table with the same record, you simply need to:

• create new column;
• insert it with null value or other thing;
• update that column after inserts with the value of the identity column.

If you need to insert the value at the same time, you can use the @@identity global variable. It'll give you the last inserted. So I think you need to do a @@identity + 1. In this case it can give wrong values because the @@identity is for all tables. So it'll count if the insert occurs in another table with identity.

Another solution is to get the max id and add one :) and you get the needed value!

Answer 5

You can do this by having a computed column in your table:

 DECLARE    @QQ TABLE (ID INT IDENTITY(1,1), Computed AS ID PERSISTED, Letter VARCHAR (1))

INSERT INTO @QQ (Letter)
VALUES ('h'),
('e'),
('l'),
('l'),
('o')

SELECT  *
FROM    @QQ

1   1   h

2   2   e

3   3   l

4   4   l

5   5   o

Answer 6

You cannot really do this - because the actual value that will be used for the IDENTITY column really only is fixed and set when the INSERT has completed.

You could however use e.g. a trigger

CREATE TRIGGER trg_YourTableInsertID ON dbo.YourTable 
AFTER INSERT
AS 
UPDATE dbo.YourTable
SET dbo.YourTable.OtherID = i.ID
FROM dbo.YourTable t2
INNER JOIN INSERTED i ON i.ID = t2.ID

This would fire right after any rows have been inserted, and would set the OtherID column to the values of the IDENTITY columns for the inserted rows. But it's strictly speaking not within the same statement - it's just after your original statement.