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

Question

Hi, 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().

Answer 1

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

Answer 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

Answer 3

Use SCOPE_IDENTITY() instead of @@IDENTITY.

See also answers to this question.