Results 1 to 3 of 3

Thread: Quick Question

  1. #1
    Join Date
    Apr 2004
    outside the rim

    Unanswered: Quick Question

    Curious what folks would consider to be the "best" way to retrieve the identity key of the record just inserted?
    This question is for discussion purposes; the business process that spurred the question is currently working.

    Using SQL Server 2008 R2, a record is inserted from a stored procedure. Let's say the sp has something like this:
                INSERT INTO tblTools
          Set @ToolNum = (Select IDENT_CURRENT('tblTools') as X)
          Return @ToolNum
    In Access, when you add a new record to the recordset, the identity field comes "pre-populated" making it east to get the actual, correct identity value assigned to the record you are inserting.
    In SQL Server, I know options include:
    among other methods.

    Each has pros and cons, such as user privileges (IDENT_CURRENT requires the user to have Select privileges on the table, and catches records created by other things, such as users and triggers), and the other two give you the last key inserted and don't allow specifying the object (which is a problem if the insert added records to multiple tables, or you have multiple inserts).

    So, the question is: what are some preferred methods for doing this basic task?
    have fun!

    Todd S.
    click to email

    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET

    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  2. #2
    Join Date
    Nov 2004
    Provided Answers: 4
    In this case I'd use
    SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

    IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).

    SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

    For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.

    Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.
    IDENT_CURRENT('tblTools') returns the value of last inserted record in the table 'tblTools'. You have no guarantee that it will return the value assigned to the id in the INSERT statement of the sp. You can even execute IDENT_CURRENT('tblTools') right now or tomorrow, and see what the last generated id on that table was, either by the INSERT in this sp, by manually entering an INSERT in MSSMS or because a trigger was fired that INSERTS into this tabel, ...
    Yet, it will run just fine if the sp is never called in parallel. The day someone creates a trigger that INSERTS in that table though, you'll have a bug that is almost impossible to reproduce.

    When you do set-based INSERTs, SCOPE_IDENTITY() still only returns one value. This is code I wrote for a datamigration project before I learned how to do it in SSIS. The names of the tables and columns have been obfuscated somewhat.

    if exists (select * from dbo.sysobjects where id = object_id(N'dm.link_IU2C_insxxx') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    	DROP TABLE dm.link_IU2C_insxxx
    CREATE TABLE dm.link_IU2C_insxxx(
    	IU_insxxx		BIGINT	NOT NULL,
    	IU_centxxx		INT	NOT NULL,
    	IU_cursxxx		CHAR(9)	NOT NULL,
    	IU_cmosxxx		BIGINT	NOT NULL,
    	IU_centrxxx		BIGINT	NOT NULL,
    	inscxxx_id		INT NOT NULL,
    	cursxxx_id		int	NOT NULL,
    	curxxx_id		INT NOT NULL,
    	bedxxx_id		INT
    PRINT 'DELETE FROM dbo.insxxx';
    DELETE FROM dbo.insxxx;
    DBCC CHECKIDENT ('dbo.insxxx', reseed, 0);
    PRINT 'INSERT INTO dbo.insxxx AND dm.link_IU2C_insxxx'
    MERGE dbo.insxxx as target
    			I.ID as IU_insxxxID
    			,verwxxx as created_at
    			,verwxxx as updated_at
    		FROM legacy_IU.dbo.Insxxx as I	--2.004.741
    			INNER JOIN dm.link_IU2C_aanxxx as LA ON
    				I.centxxx = LA.IU_centxxx AND
    				I.cursxxx = LA.IU_cursxxx AND
    				I.cmosxxx = LA.IU_cmosxxx			--377862
    			INNER JOIN dm.link_IU2C_persxxx as LP	ON
    				I.centxxx = LP.IU_centxxx AND
    				I.centrxxx = LP.IU_centrxxx
    			LEFT OUTER JOIN dm.link_IU2C_bedxxx as LB ON
    				I.centxxx = LB.IU_centxxx AND
    				I.bedxxx = LB.IU_bedrxxx	--377862
    		) AS source
    ON (1=0) -- make sure the result is False
    	INSERT (cursxxx_id
    	VALUES (source.cursxxx_id
    	OUTPUT source.IU_insxxx, source.IU_centxxx, source.IU_cursxxx, 
    			source.IU_cmosxxx, source.IU_centrxx,, 
    			INSERTED.curxxx_id, INSERTED.cursxxx_id, INSERTED.bedxxx_id
    	INTO dm.link_IU2C_insxxx (IU_insxxx, IU_centxxx, IU_cursxxx, 
    			IU_cmosxxx, IU_centrxxxID, insxxx_id, 
    			curxxx_id, cursxxx_id, bedxxx_id);
    GO -- 366680 00:21
    The link table holds the mapping from the source database primary key(s) to the id's generated by the IDENTITY column on the destination database (and some additional columns for other speed-ups and debugging).
    Last edited by Wim; 08-22-14 at 07:12.
    With kind regards . . . . . SQL Server 2000/2005/2012

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Apr 2004
    outside the rim
    Interesting. Still, it seems a lot of work and coding for such a basic functionality - I'm inserting a record, what is my identity key?

    There should be way to place a parameter into the SQL statement, something like:

    INSERT INTO tblTools
          @ToolNum = @@Iden(tblTools)
    That way I am guaranteed to get the correct key generated by my insert statement, especially if I hit more than 1 table in the process.

    I can't be the only person using the value in the identity column as part of my user facing data.

    (in case somebody comes across this in a search, the blue text is not valid)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts