Finding All identities in a multiple insert
-
Hi,
I have the following situation
create myTable ( cntr INT IDENTITY(1,1) , mystring1 varchar(10) , mystring2 varchar(10))
create anotherTable ( string1 varchar(10) , string2 varchar(10) , myNumber int)
The following stored proc can be executed by up to 200 users concurrently
create proc dbo.myProc ( @id INT)
AS
BEGIN
INSERT INTO myTable (myString1, myString2) select string1, string2 from anotherTable where myNumber = @id
-- usually this filters the rowset to fewer than 10 records (but theoretically can be 10,000)
END
what I require is a list of all the identity values associated with this transaction
so if there are 10 inserts I need 10 values returned etc.
I know @@scope_identity() will give me last record but I need more than that and I cant work back using @@rowcount because sometimes the identities are not concurrent for the transaction
any help would be much appreciated
Gareth
-
You might have to try something like this, but I hope there's an easier way:
CREATE PROCEDURE dbo.myProc ( @id INT)
AS
CREATE TABLE #holdtable
(cntr INT NOT NULL PRIMARY KEY)
BEGIN TRANSACTION
INSERT INTO #holdtable
SELECT cntr
FROM myTable
INSERT INTO myTable (myString1
, myString2)
SELECT string1
, string2
FROM anotherTable
WHERE myNumber = @id
SELECT new.cntr
FROM myTable new
WHERE NOT EXISTS (SELECT 1
FROM #holdtable old
WHERE old.cntr = new.cntr)
COMMIT TRANSACTION
GO
I don't work a lot with extended transactions, so I'm anxious to see if others agree with my solution. I don't know if you need to use an explicit table lock on 'myTable', or if you need a lock at all (depending on how your app works), but the idea is you need a before and after shot, and the ability to compare the differences.
-
Lee,
ok, I'm not sure this would work, if two users were both executing procedure and filling table at same time then this
SELECT new.cntr
FROM myTable new
WHERE NOT EXISTS (SELECT 1
FROM #holdtable old
WHERE old.cntr = new.cntr)
would list all the entries since before my insert, including mine and those of the other user.
Thankyou for your reply I appreciate it
Gareth
-
Perhaps you could add an extra field to the table you are inserting into - call it BatchID or something similar - then you just need to work out how to code a unique BatchID every time the stored proc runs - some sort of user/time algorithm should do the trick - and insert that as well. The rest is easy ...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hmmm. It might not work as coded, but not for the reasons you suggest, or so I think.
The BEGIN TRANSACTION locks out other users while you are inserting, so once you have the locks, you shouldn't need to worry about the rows inserted by others. The only thing extra you might need to do is to add an explicit TABLOCK on 'myTable' in the previous INSERT. The final SELECT should only pick up on the values that did not exist prior to your INSERT.
Given the specifications, I just don't see much of an alternative to locking other users out while your transaction gathers the information it needs.
-
Something else to consider is using a trigger on 'myTable' to isolate the new identity values, all of which will be contained in the 'inserted' virtual table. I should have thought of that right off the bat.
-
Me too! Good idea.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Phil,
yes, brilliant idea unfortunatelty the schema is locked down and cant be changed ( well at least for next 12 months).
Lee,
I am unable to lock the table because it is used to much by the current system.
I have a version that works using a cursor inside a trigger and though this is ok for small recordsets its a unworkable when the batch size is over 1000 rows
I really appreciate all this feedback
Gareth
-
Why did you need to use a cursor? Perhaps we can find a (fast) set-based approach if you provide more info.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi,
I suggest using the INSERT Trigger to populate to another table from the virtual Inserted table. I think that will take care of it.
Thanks
RN
-
Hmmm. That's quite a list of constraints. Can't lock out other users. Can't use a cursor. Can't add a trigger.
Well, I'll take another stab. This goes row by row, but eschews the cursor thing. I have syntax-checked it, but haven't tested it. Something like this ought to work, albeit slower than if you were doing this in a set operation. I still think the trigger solution is the best bet.
CREATE PROCEDURE dbo.myProc
(@id INT)
AS
DECLARE @mystring1 VARCHAR (10)
, @mystring2 VARCHAR (10)
, @rowcount INT
, @seq INT
, @maxseq INT
, @cntr INT
CREATE TABLE #newcntrs
(cntr INT NOT NULL)
CREATE TABLE #holdtable
(seq INT IDENTITY NOT NULL PRIMARY KEY
, myString1 VARCHAR (10) NOT NULL
, myString2 VARCHAR (10) NOT NULL)
INSERT INTO #holdtable (myString1
, myString2)
SELECT mystring1
, mystring2
FROM anotherTable
WHERE myNumber = @id
SELECT @maxseq = IDENT_CURRENT ('#holdtable')
, @seq = 0
WHILE @seq < @maxseq
BEGIN
SELECT @seq = @seq + 1
SELECT @mystring1 = mystring1
, @mystring2 = mystring2
FROM #holdtable
WHERE seq = @seq
SELECT @rowcount = @@ROWCOUNT
IF @rowcount != 1
--THEN
CONTINUE
--END IF
INSERT INTO myTable (mystring1
, mystring2)
VALUES (@mystring1
, @mystring2)
SELECT @cntr = SCOPE_IDENTITY ()
INSERT INTO #newcntrs
VALUES (@cntr)
END
SELECT cntr
FROM #newcntrs
ORDER BY cntr
GO
-
All,
thanks for your input.
I will work on a variation of Lee's stored proc and stick with the trigger until I get it working
Thanks for everything
gareth
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply