执行插入后,我选择SCOPE_IDENTITY或@@ IDENTITY.
SCOPE_IDENTITY返回null,但@@ IDENTITY不返回.
我不明白这是怎么可能的.
你能想到这样的原因吗？
解决方法
以下是SCOPE_IDENTITY()将为null但@@ IDENTITY将具有值的示例：
insert into a table with no identity,
that table has an insert trigger that
then inserts into a history table with
an identity. SCOPE_IDENTITY() will be
null (no identity in the local scope),
but @@IDENTITY will report the
identity from the trigger.
FYI,有一个已知的错误SCOPE_IDENTITY()：https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328811
您最好的使用身份验证是使用OUTPUT INTO,它可以捕获一组ID,并且不受SCOPE_IDENTITY()错误的限制：
declare @x table (tableID int identity not null primary key,datavalue varchar(10))
declare @y table (tableID int,datavalue varchar(10))
INSERT INTO @x values ('aaaa')
INSERT INTO @x values ('bbbb')
INSERT INTO @x values ('cccc')
INSERT INTO @x values ('dddd')
INSERT INTO @x values ('eeee')
INSERT INTO @x
(datavalue)
OUTPUT INSERTED.tableID,INSERTED.datavalue --<<<<OUTPUT INTO Syntax
INTO @y --<<<<OUTPUT INTO Syntax
SELECT
'value='+CONVERT(varchar(5),dt.NewValue)
FROM (SELECT id as NewValue from sysobjects where id<20) dt
ORDER BY dt.NewValue
select * from @x
select * from @y
