检索使用Uniqueidentifier插入的最后一行，它不是IDENTITY [英] Retrieve last row inserted with Uniqueidentifier that it is not IDENTITY

问题描述

我对一个查询感到困惑。

I am puzzled with a query.

我需要找出添加到表中的LAST行，其中数据类型为Uniqueidentifier
列的是：
aspnet_Applications.ApplicationId
注意：此列Uniqueidentifier它是NOT和IDENTITY列。

I need find out the LAST row added in a table with a column with datatype Uniqueidentifier column is: aspnet_Applications.ApplicationId Notes: This column Uniqueidentifier it is NOT and IDENTITY Column.

我还需要插入最后一行，并更新它不同的表
aspnet_Users.ApplicationId

I need also take the last row inserted and update it on a different table aspnet_Users.ApplicationId

我试图在MS SQL 2008中使用SCOPE_IDENTITY但不工作，因为SCOPE_IDENTITY只与IDENTITY列一起工作。

I tried to use SCOPE_IDENTITY in MS SQL 2008 but does not work because SCOPE_IDENTITY is working only with IDENTITY column.

这里是我的代码。任何想法？

Here 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()
);

推荐答案

即使您已经在ApplicationID上有一个DEFAULT值，您可以这样做：

It's a little more work, but for your inserts, even though you already have a DEFAULT value on the ApplicationID, you could 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

基本上，您先设置GUID，已经知道你将要插入什么。

Essentially, you set the GUID beforehand, so you already know what you will be inserting.

这篇关于检索使用Uniqueidentifier插入的最后一行，它不是IDENTITY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！