Return (value) from last insert statement

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)

Return (value) from last insert statement

Author

Topic

cmaso
Starting Member

5 Posts

Posted - 2007-12-05 : 17:51:18

Is there a function similar to scope_identity(), that can be used to return any given column value from the most recent insert?

What I'm trying to do is capture a value, that's NOT an identity column for the record, immediately after that record is inserted. This column is set to have a default value of newID() when no value is passed into the insert statement, and when that's the case, I need to be able to capture that newly created value, similarly to how you would if it were an identity column.

So the table looks something like:
messageUid uniqueidentifier default value: newid()
senderId int
receiverId int
messageDate timestamp default value: getDate()
threadUid uniqueidentifier default value: newid()

and what I want to do would like something look like:
declare @thread uniqueidentifier

insert into messages (senderId,receiverId)
values (24235,56221)

set @thread=.... somefunction()

Thanks!
C Maso

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-05 : 17:59:07

Here's how you do it:

declare @thread uniqueidentifier
set @thread = newid()

insert into messages (messageuid,senderId,receiverId)
values (@thread, 24235,56221)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-06 : 01:53:32

Also refer http://vyaskn.tripod.com/retrieve_guid_value_like_identity.htm

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-06 : 04:15:14

You can get the id of entered column as follows:-

create table #t
( ID uniqueidentifier default newid(),
Name varchar(50)
)

INSERT #t (Name)
OUTPUT INSERTED.ID
SELECT 'U' UNION ALL
SELECT 'T' UNION ALL
SELECT 'M'...

and you get generated ids..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources