//
you're reading...
Database, IT-Center, SQL Server

SQL Server Last ID Identity

Assuming a simple table:

CREATE TABLE dbo.foo(ID INT IDENTITY(1,1), name SYSNAME);

We can capture IDENTITY values in a table variable for further consumption.

DECLARE @IDs TABLE(ID INT);

-- minor change to INSERT statement; add an OUTPUT clause:
INSERT dbo.foo(name) 
  OUTPUT inserted.ID INTO @IDs(ID)
SELECT N'Fred'
UNION ALL
SELECT N'Bob';

SELECT ID FROM @IDs;

The nice thing about this method is (a) it handles multi-row inserts (SCOPE_IDENTITY() only returns the last value) and (b) it avoids this parallelism bug, which can lead to wrong results, but so far is only fixed in SQL Server 2008 R2 SP1 CU5.

 

About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 9 other followers

September 2014
M T W T F S S
« Jun   Oct »
1234567
891011121314
15161718192021
22232425262728
2930  

Archives

Web Statistic

Blog Stats

  • 149,551 hits
%d bloggers like this: