CODEWITHSUNDEEP
sql-serverinsert
Andy
Andy

Reputation: 2762

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

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

Upvotes: 1

Views: 200

Answers (3)

devio
devio

Reputation: 37215

Use SCOPE_IDENTITY() instead of @@IDENTITY.

See also answers to this question.

Upvotes: 0

AaronM
AaronM

Reputation: 292

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

Upvotes: 2

bashmohandes
bashmohandes

Reputation: 2376

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

Upvotes: 0

Related Questions