Is there a better way to retrieve unique ID upon INSERT?

Question

I am using the following SQL Server statement to insert records and return the unique auto-inc ID value all in one shot. Wonder if there is a simpler/faster/better way to do it?

SET NOCOUNT ON;

DECLARE @res table (ID bigint);

INSERT INTO [Titles] ([Name],[Timestamp],[Title])
OUTPUT INSERTED.ID INTO @res
VALUES ('Name','23 aug 2010','this is a title');

SELECT [ID] FROM @res;

UPD: The solution that suits me best is the following:

INSERT INTO [TestCase] ([Name],[Tags],[Timestamp],[Title])
OUTPUT INSERTED.*
VALUES ('Hello',0x22324423,'23 aug 2010','this is a title');

Very code-generation friendly. You can easily generate these statements for any data structure through reflection. Also works very well as a substitute for Linq2SQL Dataset.InsertOnSubmit().

duplicate of http://stackoverflow.com/questions/481395/t-sql-identity-scope-identity-output-and-other-methods-of-retrieving-last — devio, Apr 24 '10 at 06:42

score 2 · Accepted Answer · answered Apr 24 '10 at 06:39

2

Simpler:

INSERT INTO [Titles] ([Name],[Timestamp],[Title]) 
OUTPUT INSERTED.ID VALUES ('Name','23 aug 2010','this is a title');

http://msdn.microsoft.com/en-us/library/ms177564.aspx

answered Apr 24 '10 at 06:39

AaronM

292
1
2
10

score 0 · Answer 2 · answered Apr 24 '10 at 06:35

0

This is an example of using @@Identity

INSERT INTO Production.Location (Name, CostRate, Availability, ModifiedDate)
VALUES ('Damaged Goods', 5, 2.5, GETDATE());
GO
SELECT @@IDENTITY AS 'Identity';

for more information check here

answered Apr 24 '10 at 06:35

bashmohandes

2,356
1
16
23

score 0 · Answer 3 · edited May 23 '17 at 09:57

0

Use SCOPE_IDENTITY() instead of @@IDENTITY.

Is there a better way to retrieve unique ID upon INSERT?

3 Answers3