1

This has been troubling me for days, I have a table with auto-increment, what I want is that the value of the increment value will be inserted in the same row. The problem is that when concurrent users are accessing the query, it will not copy the correct value.

Below is my table:

CREATE TABLE tblTransactions
  (
     dctransno     INT IDENTITY PRIMARY KEY,
     vcDescription VARCHAR,
     dcTransNoCopy INT NULL,
     user          VARCHAR        
  )

and my query:

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

What I want to achieve is that I can have a grouped transaction like below example. The problem is that my above query doesn't take ownership. Assuming userA and userB are simultaneously using the system:

       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.

Please help, thanks in advance.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
popoyjin
  • 36
  • 5
  • I assume dcTransNoCopy is CloneTransNo and dcTransNo is just TransNo? Your sample data and supplied code use different names. Also, if you use scope_identity() does this solve the problem? I suspect it will – billinkc Feb 18 '14 at 13:03
  • Yes, sorry dcTransNoCopy is CloneTransNo and dcTransNo is just TransNo. – popoyjin Feb 18 '14 at 13:11
  • The code you have shown us `SET dcTransNoCopy = @identity WHERE dcTransNo = @identity` can never result in `4,3`. Both columns will always have the same value. – Martin Smith Feb 18 '14 at 13:43
  • Hi Martin, it's actually happening on my side and has been driving me nuts for days. – popoyjin Feb 18 '14 at 13:50
  • 1
    Well it ain't the code you've shown us that is doing it. That can never be responsible for `dcTransNoCopy` having a different value from `dcTransNo`. There must be more to this that you haven't shown us. – Martin Smith Feb 18 '14 at 13:55

2 Answers2

1

You need to use SCOPE_IDENTITY() instead of @@identity

Further info here

SteveB
  • 1,474
  • 1
  • 13
  • 21
  • Hi bhs, I already tried using scope_identity that's why I switched to @@identity but I did not add begin tran in my query before. I'll see what happens. By the way, thanks for the quick reply. – popoyjin Feb 18 '14 at 13:07
  • no problem - scope_identity() is for exactly this purpose though so it should work :-) – SteveB Feb 18 '14 at 13:23
0

Why aren't you using output table ? 

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

this is from http://www.sqlteam.com/article/using-the-output-clause-to-capture-identity-values-on-multi-row-inserts

Mathese F
  • 559
  • 4
  • 9
  • Hi Mathese, thanks for these, been ignorant about output. Does this replace the scope_identity() to effectively capture the identity within the transaction? – popoyjin Feb 18 '14 at 13:42
  • Hello, yes you don't have to use the scope_identity anymore. Microsoft classify way to find identity like this : @@identity not safe inside same pid, scope_identifie not safe with different pid, output best practise. Take a look at the article it's coming from :) – Mathese F Feb 18 '14 at 14:11
  • I'm now changing my query replaced with output. Will monitor the results. Thanks a lot! – popoyjin Feb 18 '14 at 14:14