CODEWITHSUNDEEP
sql-server-2008sql-insert
Code Ratchet
Code Ratchet

Reputation: 6029

Return UniqueIdentifier of newly inserted row

I have the following insert statement: 

INSERT INTO [User].User_Profile

 (UniqueId, Username, EmailAddress,
  Password, BirthDay, BirthMonth,
  BirthYear, Age, AccountType, DateCreated,
  DeletedDate, DeletedReason, ProfileStatus)

  VALUES

  (NEWID(), @Username, @EmailAddress,
  @Password, @BirthDay, @BirthMonth,
  @BirthYeat, @Age, 1, SYSDATETIME(),
  null, null, 2)

  SELECT @@IDENTITY

The @@IDENTITY returns me the newly inserted row ID for the particular user but I need to return the NEWID() value after the insert statement has been executed as I need this NEWID() for other functionality inside the website but I'm un-certain how to retrieve it and as my SQL skills aren't great I was hoping someone could help me.

Upvotes: 0

Views: 2586

Answers (1)

vhadalgi
vhadalgi

Reputation: 7189

Using OUTPUT clause:

INSERT INTO [User].User_Profile

 (UniqueId, Username, EmailAddress,
  Password, BirthDay, BirthMonth,
  BirthYear, Age, AccountType, DateCreated,
  DeletedDate, DeletedReason, ProfileStatus)

OUTPUT INSERTED.UniqueId
  VALUES

  (NEWID(), @Username, @EmailAddress,
  @Password, @BirthDay, @BirthMonth,
  @BirthYeat, @Age, 1, SYSDATETIME(),
  null, null, 2)

It can also be inserted into a table like this:

declare @output(id uniqueidentifier)
 INSERT INTO [User].User_Profile

     (UniqueId, Username, EmailAddress,
      Password, BirthDay, BirthMonth,
      BirthYear, Age, AccountType, DateCreated,
      DeletedDate, DeletedReason, ProfileStatus)

    OUTPUT INSERTED.UniqueId into @output(id)
      VALUES

      (NEWID(), @Username, @EmailAddress,
      @Password, @BirthDay, @BirthMonth,
      @BirthYeat, @Age, 1, SYSDATETIME(),
      null, null, 2)

Upvotes: 3

Related Questions