我对一个查询感到困惑.
我需要找到一个表中添加的LAST行,其中包含一个数据类型为Uniqueidentifier的列
专栏是：
aspnet_Applications.ApplicationId
注意：此列Uniqueidentifier不是和IDENTITY列.
我还需要插入最后一行并在另一个表上更新它
aspnet_Users.ApplicationId
我尝试在MS sql 2008中使用SCOPE_IDENTITY但不起作用,因为SCOPE_IDENTITY仅适用于IDENTITY列.
在这里我的代码.有任何想法吗？
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,UNIQUE NONCLUSTERED
(
[ApplicationName] ASC
)WITH (PAD_INDEX = OFF,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,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','',sysutcdatetime()
);
解决方法
这是一个更多的工作,但对于您的插入,即使您已经在ApplicationID上有一个DEFAULT值,您可以这样做：
DECLARE @MyIdentity uniqueidentifier;
SET @MyIdentity = NewID();
INSERT INTO dbo.aspnet_Applications
(
ApplicationName,ApplicationId,@MyIdentity,'Dummy text'
);
SELECT @MyIdentity
基本上,您事先设置了GUID,因此您已经知道要插入的内容.
