SQL Tips - Output within Insert - Very useful to get last UniqueIdentifier

Each time we want to get the last value inserted for an Identity or UniqueIdentifier field in our SQL table, we face a problem.
In the case of Identity fields, we can recover the last value easily using the SQL function @@identity; However, there is no SQL function to recover a uniqueidentifier field since it is not an incremental field, so what to do in such case?

It is pretty simple, the only thing we need to know is that we are able to recover any value JUST inserted into a table using OUTPUT.

Example:
1) My table definition:


1 CREATE TABLE [dbo].[Synergy_Menu](
2 [SYME_SEQ] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_Synergy_Menu_SYME_SEQ] DEFAULT (newid()),
3 [SYME_PARENT] [uniqueidentifier] NULL,
4 [SYME_DESC] [varchar](100) NULL,
5 [SYME_POS] [int] NULL,
6 [SYME_URL] [varchar](500) NULL,
7 [SYME_PAGE] [varchar](100) NULL,
8 [SYME_TYPE] [int] NULL,
9 [Active] [bit] NULL CONSTRAINT [DF_Synergy_Menu_Active] DEFAULT ((1)),
10 [CreatedBY] [varchar](255) NULL CONSTRAINT [DF_Synergy_Menu_CreatedBY] DEFAULT ('web\user'),
11 [CreatedDate] [datetime] NULL CONSTRAINT [DF_Synergy_Menu_CreatedDate] DEFAULT (getdate()),
12 [Visible] [int] NULL CONSTRAINT [DF_Synergy_Menu_Visible] DEFAULT ((1)),
13 CONSTRAINT [PK_Synergy_Menu] PRIMARY KEY CLUSTERED
14 (
15 [SYME_SEQ] ASC
16 )


2) Getting the last inserted UniqueIdentifier value:


1 DECLARE @OutputTable TABLE (SYME_SEQ UNIQUEIDENTIFIER)
2
3 insert into dbo.synergy_menu
4 (SYME_PARENT,SYME_DESC,SYME_POS,
5 SYME_URL,SYME_PAGE,SYME_TYPE,
6 Active,CreatedBY,Visible)
7 output inserted.SYME_SEQ INTO @OutputTable
8 SELECT @TextMenuOption,SYME_DESC,
9 SYME_POS,SYME_URL,
10 SYME_PAGE,@Type,Active, @User, Visible
11 FROM dbo.synergy_menu
12 where SYME_SEQ = @MenuOption
13 and Active = 1
14
15 SELECT * from @OutputTable

3) Just try it!