So I need to implement essentially an auto-increment column. I have a column in one table that has an int that will act as the counter, and there will be another table with a corresponding number of rows, each row having it's own counter. I thought this would be fairly simple to implement, but after doing some research I found a few forums where they mention there could be issues in situations like this if multiple transactions were running roughly at the same time, even by running the select,update, & insert in one transaction
After looking into though it seemed to me that if I used the OUTPUT clause to grab the new value the first table is getting updated with, I could avoid that issue entirely.
So this is the T-SQL I'm thinking of. Should this be okay for my situation?
BEGIN TRANSACTION;
DECLARE @tblCtrs table (Ctr int)
DECLARE @CtrVal INT
UPDATE Table1
SET Ctr = Ctr+1
OUTPUT inserted.Ctr INTO @CtrVals
WHERE id=<whatever id value>
SET @CtrVal = (SELECT TOP(1) Ctr FROM @tblCtrs)
INSERT INTO TABLE2 (Ctr, ....)
VALUES( @CtrVal, ....)
COMMIT;
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…