Thread: Quick Question
08-21-14, 16:00 #1Registered User
- 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:Code:
BEGIN BEGIN TRANSACTION INSERT INTO tblTools ([Desc],CreateDate,Model,CreatedBy,Notes) VALUES (@Desc,@CreateDate,@Model,@CreatedBy,@Notes); COMMIT TRANSACTION Set @ToolNum = (Select IDENT_CURRENT('tblTools') as X) Return @ToolNum END
In SQL Server, I know options include:Code:
IDENT_CURRENT('tblX') SCOPE_IDENTITY @@IDENTITY
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!
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 ...
08-22-14, 04:35 #2Registered User
Provided Answers: 4
- Join Date
- Nov 2004
In this case I'd use
SET @ToolNum = SCOPE_IDENTITY()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.
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 ) GO 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 USING (SELECT I.ID as IU_insxxxID ,LA.IU_cenxxx ,LA.IU_curxxx ,LA.IU_cmoxxx ,LP.IU_cenxxx ,LA.curxxx_id ,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 WHEN NOT MATCHED THEN INSERT (cursxxx_id ,created_at ,updated_at ,curxxx_id ,status ,bedxxx_id ,insxxx ,uitxxx ) VALUES (source.cursxxx_id ,source.created_at ,source.updated_at ,source.curxxx_id ,source.status ,source.bedxxx_id ,source.insxxx ,source.uitxxx ) OUTPUT source.IU_insxxx, source.IU_centxxx, source.IU_cursxxx, source.IU_cmosxxx, source.IU_centrxx, INSERTED.id, 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 GO
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
08-25-14, 12:15 #3Registered User
- 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 ([Desc],CreateDate,Model,CreatedBy,Notes) VALUES (@Desc,@CreateDate,@Model,@CreatedBy,@Notes); SET @ToolNum = @@Iden(tblTools)
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)