Get Primary Key - SQL Server Forums

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)

Get Primary Key

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-03-16 : 10:37:30

Hello,

I am creating a new record as follows:
INSERT dbo.Documents (DocumentName, DocumentCreatedDate)
VALUES (@DocumentName, GETUTCDATE())

I need to return the value of the DocumentId just created.

How can I do this?

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 10:38:51

select scope_identity()

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 10:40:23

quote:
SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

Peter Larsson
Helsingborg, Sweden

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-03-16 : 11:31:51

Thanks Peter,
Miguel

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-03-16 : 12:03:15

Oops,

I got the error:
Operand type clash: numeric is incompatible with uniqueidentifier

When doing:
@BlogId UNIQUEIDENTIFIER OUTPUT
...
SELECT @BlogId = SCOPE_IDENTITY()

Can't SCOPE_IDENTITY be used with GUID's?

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 12:08:40

I don't think so.

You will have to change your code to

declare @nid uniqueidentifier
set @nid = newid()

INSERT dbo.Documents (documentid, DocumentName, DocumentCreatedDate)
VALUES (@nid, @DocumentName, GETUTCDATE())

Peter Larsson
Helsingborg, Sweden

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-16 : 16:38:37

I'm not sure about performace, but you can use the OUTPUT clause to get the GUID that was just inserted.

For example:

DECLARE @GloballyUniqueData AS TABLE
(
	GUID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL DEFAULT (NEWSEQUENTIALID())
);
DECLARE @MyTable TABLE (ID uniqueidentifier)

INSERT INTO @GloballyUniqueData 
OUTPUT INSERTED.GUID INTO @MyTable
DEFAULT VALUES

SELECT * FROM @MyTable

Cheers,

-Ryan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources