Get the last row inserted with Uniqueidentifier so that it is not IDENTITY

Question

I am puzzled by the request.

I need to know the LAST row added to the table, with a column with the Uniqueidentifier data type column: aspnet_Applications.ApplicationId Notes: This Uniqueidentifier column is NOT and IDENTITY Column.

I need to also insert the last row and update it in another aspnet_Users.ApplicationId table

I tried to use SCOPE_IDENTITY in MS SQL 2008, but it does not work, because SCOPE_IDENTITY only works with the IDENTITY column.

Here is my code. Any ideas?

CREATE DATABASE Test; GO USE Test; GO -- Create entities CREATE TABLE [dbo].[aspnet_Applications]( [ApplicationName] [nvarchar](256) NOT NULL, [LoweredApplicationName] [nvarchar](256) NOT NULL, [ApplicationId] [uniqueidentifier] NOT NULL, [Description] [nvarchar](256) NULL, PRIMARY KEY NONCLUSTERED ( [ApplicationId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], UNIQUE NONCLUSTERED ( [LoweredApplicationName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], UNIQUE NONCLUSTERED ( [ApplicationName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[aspnet_Applications] ADD DEFAULT (newid()) FOR [ApplicationId] GO CREATE TABLE [dbo].[aspnet_Users]( [ApplicationId] [uniqueidentifier] NOT NULL, [UserId] [uniqueidentifier] NOT NULL, [UserName] [nvarchar](256) NOT NULL, [LoweredUserName] [nvarchar](256) NOT NULL, [MobileAlias] [nvarchar](16) NULL, [IsAnonymous] [bit] NOT NULL, [LastActivityDate] [datetime] NOT NULL, PRIMARY KEY NONCLUSTERED ( [UserId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[aspnet_Users] WITH CHECK ADD FOREIGN KEY([ApplicationId]) REFERENCES [dbo].[aspnet_Applications] ([ApplicationId]) GO ALTER TABLE [dbo].[aspnet_Users] ADD DEFAULT (newid()) FOR [UserId] GO ALTER TABLE [dbo].[aspnet_Users] ADD DEFAULT (NULL) FOR [MobileAlias] GO ALTER TABLE [dbo].[aspnet_Users] ADD DEFAULT ((0)) FOR [IsAnonymous] GO -- Add data DECLARE @MyIdentity binary(16); INSERT INTO dbo.aspnet_Applications ( ApplicationName, LoweredApplicationName, Description ) VALUES ( 'x', 'x', 'Dummy text' ); SET @MyIdentity = SCOPE_IDENTITY(); -- DOES NOT WORK PRINT @MyIdentity; -- DOES NOT WORK INSERT INTO dbo.aspnet_Users ( ApplicationId, UserName, LoweredUserName, MobileAlias, IsAnonymous, LastActivityDate ) VALUES ( @MyIdentity, 'Administrator', 'administrator', '', 0, sysutcdatetime() ); 

Answer 1

A bit more work, but for your inserts, even if you already have a DEFAULT value for ApplicationID, you can do this:

 DECLARE @MyIdentity uniqueidentifier; SET @MyIdentity = NewID(); INSERT INTO dbo.aspnet_Applications ( ApplicationName, LoweredApplicationName, ApplicationId, Description ) VALUES ( 'x', 'x', @MyIdentity, 'Dummy text' ); SELECT @MyIdentity 

Essentially, you set the GUID in advance, so you already know what you will embed.

Answer 2

You can use the OUTPUT clause to return the inserted values, but the LBT response is probably simpler and more efficient.

 DECLARE @ids table(id uniqueidentifier) INSERT INTO dbo.aspnet_Applications ( ApplicationName, LoweredApplicationName, Description ) OUTPUT INSERTED.ApplicationId into @ids VALUES ( 'x', 'x', 'Dummy text' ) 

By the way, if not for the foreign key, you can use compound DML for this, as shown below.

 INSERT INTO dbo.aspnet_Users ( ApplicationId , UserName , LoweredUserName, MobileAlias , IsAnonymous , LastActivityDate ) SELECT ApplicationId , 'Administrator', 'administrator', '' , 0 , sysutcdatetime() FROM ( INSERT INTO dbo.aspnet_Applications ( ApplicationName , LoweredApplicationName, Description ) OUTPUT INSERTED.ApplicationId VALUES ( 'x', 'x', 'Dummy text' ) ) AS I