Skip to main content
B
BACFL
Inspiring
August 17, 2011
Answered

Get last Uniqueidentifier inserted in SQL Server table

  • August 17, 2011
  • 2 replies
  • 8263 views

I am looking for code that will get me the last entered ID in a table.  I am using SQL Server and have the ID's set as uniqueidentifiers.  I tried SCOPE_IDENTITY() and had an issue.  Also, want to make sure it is for that unique insert, not an insert that slipped in almost concurrently.

Thanks!

BCo

This topic has been closed for replies.
Correct answer -__cfSearching__-
I am looking for code that will get me the last entered ID in a table.  I am using SQL Server and have the ID's set as uniqueidentifiers.  I tried SCOPE_IDENTITY() and had an issue.  Also, want to make sure it is for that unique insert, not an insert that slipped in almost concurrently.

SCOPE_IDENTITY() only works with identity columns.  If your table does not contain an identity column that function will be of no use to you. Neither will cfquery's "result" attribute as it only works with ms sql identity values too.

AFAIK, there is no built in method for automatically returning a newly created uniqueidentifier.  The closest you can get in MS SQL 2005, without involving triggers, is using an OUTPUT clause.

     SET NOCOUNT ON
     INSERT INTO yourTable ( ColumnA, ColumnB )
     OUTPUT inserted.yourIDColumn
     VALUES ('foo', 'bar')

     SET NOCOUNT OFF

Message was edited by: -==cfSearching==-

2 replies

-
-__cfSearching__-Correct answer
Inspiring
August 18, 2011
I am looking for code that will get me the last entered ID in a table.  I am using SQL Server and have the ID's set as uniqueidentifiers.  I tried SCOPE_IDENTITY() and had an issue.  Also, want to make sure it is for that unique insert, not an insert that slipped in almost concurrently.

SCOPE_IDENTITY() only works with identity columns.  If your table does not contain an identity column that function will be of no use to you. Neither will cfquery's "result" attribute as it only works with ms sql identity values too.

AFAIK, there is no built in method for automatically returning a newly created uniqueidentifier.  The closest you can get in MS SQL 2005, without involving triggers, is using an OUTPUT clause.

     SET NOCOUNT ON
     INSERT INTO yourTable ( ColumnA, ColumnB )
     OUTPUT inserted.yourIDColumn
     VALUES ('foo', 'bar')

     SET NOCOUNT OFF

Message was edited by: -==cfSearching==-

B
BACFLAuthor
Inspiring
August 19, 2011

I went with a CF solution.  What do you think about IDENITITYCOL?  It seems to be working.  But my question is whether the DB is locked and will get the correct ID or if it is possible another one could be entered before the query to get the "ID".

SELECT itemid
FROM items
WHERE itemid = #insertitemid['IDENTITYCOL']#

Thanks again everyone for the help!

-
-__cfSearching__-
Inspiring
August 19, 2011

SELECT itemid

FROM items

WHERE itemid = #insertitemid['IDENTITYCOL']#

I am assuming that is a typo. Because there is no reason to re-SELECT the last id when you already have it ;-)

But my question is whether the DB is locked and will get the

correct ID or if it is possible another one could be entered

before the query to get the "ID".

There is no locking of the database. IIRC the result attribute of cfquery returns the correct ID by using scope_identity() internally.

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

glamorous_Wonder6C1C
glamorous_Wonder6C1C
Inspiring
August 17, 2011

Hi,

You can use result attribute of cfquery tag to get the id of last inserted record.

<cfquery name="testqry" datasource="somesource" result="restestqry">

    write query for insertion     

</cfquery>

<cfdump var="#restestqry#">

you will see the last inserted record id in dump result.

For details please look at documentation of coldfusion.

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Part_3_CFML_Ref_1.html

Thanks

saurav

B
BACFLAuthor
Inspiring
August 17, 2011

Thanks for the answer, but I think I am looking for something without using CFDUMP and is in the SQL Statement.

B
BACFLAuthor
Inspiring
August 17, 2011

Alternately, if I use <cftransaction> around the input and the select queries am I guaranteed to get the last ID?

Thanks again!

B.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices