Getting a NEWSEQUENTIALID()

I ran into a problem yesterday where I needed to get the ID that was created after an insert.  Typically this is something you use @@identity for or Scope_Identity().  I had to use a  Guid Though so I couldn’t use those features.  I posted the question on twitter and received a quick response a BIG Thank you to.  @robboek , @venzann and @mladenPrajdic.  For the suggestion on using the Output Clause

Here’s a Standard use of @@identity. 

Declare @identity int

Create table #temp
    (Id int identity(1,1) not null,
    ProductName varchar(255) null)
Insert into #temp
    (Productname) Values (‘Bacon Cheeseburger’)
Select @identity = @@IDENTITY

Select @identity

This doesn’t work if your using a Guid and a NewSequentialID  but here is the fix. 

Create table #temp
    (Id uniqueidentifier Default newsequentialid(),
    Productname varchar(255) null,
    Cost money not null)
Declare @RowNumber Table (rowid uniqueidentifier)
Declare @rowid uniqueidentifier

    Insert into #temp
        (Productname,Cost)
        Output inserted.Id
            Into @RowNumber
    Values
        (‘Bacon Cheeseburger’,’2.99′)
    Select @rowid = rowid from @RowNumber
    Select @rowid

This solution should work on Sql 2005 or Sql 2008

 

Pat

Advertisements

One response to “Getting a NEWSEQUENTIALID()

  1. thanks. I know another way of sp-sql-database-recovery. maybe you will be interested

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