这已经困扰了我好几天,我有一个自动递增的表格,我想要的是增量值的值将插入同一行。问题是,当并发用户访问查询时,它不会复制正确的值。
下面是我的表格:
CREATE TABLE tblTransactions
(
dctransno INT IDENTITY PRIMARY KEY,
vcDescription VARCHAR,
dcTransNoCopy INT NULL,
user VARCHAR
)
和我的查询:
BEGIN TRAN
INSERT INTO tblTransactions
(dctransno,
user)
VALUES (@vcDescription,
@user);
SET @identity = @@IDENTITY -- get the identity
UPDATE tblTransactions
SET dcTransNoCopy = @identity
WHERE dcTransNo = @identity
AND user = @user; -- insert
COMMIT TRAN
我想要实现的是我可以有一个分组事务,如以下示例所示。问题是我上面的查询没有所有权。假设用户 A 和用户 B 同时使用系统:
dcTransNo Description dcTransNoCopy User
1 Transaction1 1 userA
2 Transaction2 1 userA
3 Transaction3 1 userA
4 Transaction1 3 userB ---> not correct, ClonetransNo value for userB should be 4 not 3.
请帮忙,提前谢谢。
你需要
使用 SCOPE_IDENTITY() 而不是 @@identity
更多信息在这里
为什么不使用输出表?
DECLARE @InsertedRows TABLE (ProductID INT, ProductNumber NVARCHAR(25) )
INSERT Product ([Name], ProductNumber, ListPrice)
OUTPUT inserted.ProductID, inserted.ProductNumber
INTO @InsertedRows
SELECT [Name], ProductNumber, ListPrice
FROM ProductsToInsert AS I
WHERE NOT EXISTS (SELECT 1 FROM Product WHERE ProductNumber = I.ProductNumber)
UPDATE ProductsToInsert
SET InsertedIdentityValue = T.ProductID
FROM ProductsToInsert I
JOIN @InsertedRows T ON T.ProductNumber = I.ProductNumber
SELECT RowID, ProductNumber, InsertedIdentityValue
FROM ProductsToInsert
WHERE InsertedIdentityValue IS NOT NULL
这是来自 http://www.sqlteam.com/article/using-the-output-clause-to-capture-identity-values-on-multi-row-inserts