Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 uniqueidentifier colum of the last inserted row

Author  Topic 

johnsql
Posting Yak Master

161 Posts

Posted - 2009-01-24 : 15:08:18
Hi,
I have a table with a primary column of the uniqueidentifier type. Whenever I insert a new row to the table, I want to return the value of the uniqueidentifier column of the last-inserted row. Please tell me how can I get that column value?

I know that if a column is of the int (identity), then to get the last-inserted identity value, I can use @@IDENTITY. But in my case, the primary column is of the uniqueidentifire type, and I do not know how to do.

Thanks,
johnsql

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-01-24 : 16:36:37
You can use the output clause of the insert statement.

create table TestTable (id uniqueidentifier, descr varchar(10))
go
insert into TestTable output inserted.id values(newid(),'abcd')
go

If you want to get the output into to a variable, you can use a table variable like this:

declare @outputTable table ( id uniqueidentifier);
insert into TestTable output inserted.id into @outputTable values(newid(),'abcd')
select * from @outputTable

There is more info here:
http://msdn.microsoft.com/en-us/library/ms177564.aspx
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-25 : 04:48:23
if you want the unique identifier value to be sequential, use NEWSEQUENTIALID() instead of NEWID()

http://msdn.microsoft.com/en-us/library/ms189786(SQL.90).aspx
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-25 : 08:11:20
DECLARE @u UNIQUEIDENTIFIER

SET @u = NEWID()


INSERT ...
SELECT @u, ... FROM ...




E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

jeffreymfischer
Starting Member

10 Posts

Posted - 2009-10-07 : 11:16:52
I've written a detailed article on an enterprise-ready unique identifier solution.

http://blog.scoftware.com/post/2009/08/29/SQL-Server-UniqueIdentifier-Globally-Unique-Globally-Sequential-SOLUTION.aspx

Read it and provide feedback.

Jeff Fischer

Scoftware Achitect/Developer
http://blog.scoftware.com

Scoftware Achitect/Developer
http://blog.scoftware.com
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-10-08 : 10:00:36
quote:
Originally posted by jeffreymfischer

I've written a detailed article on an enterprise-ready unique identifier solution.



Is it WhiteFang™ approved?


http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page
   

- Advertisement -