OK, I've come up with at least one way to do it.

I've added two columns to the import table, [DupeCandidateImport].

[SequenceSeed] [int] IDENTITY(0,1) NOT NULL,

[AppSequenceNumber] [int] NULL

Before I import data I'll do

TRUNCATE TABLE [DupeCandidateImport]

GO

DBCC CHECKIDENT ('DupeCandidateImport', RESEED, 0)

GO

It's my understanding that have the seed value of the identity column defined as 0 requires the explicit call to DBCC CHECKIDENT to start the column over at 0. I guess I could avoid that by offsetting my update described below by -1, but if this works it seems better (sort of).

When I import the new candidates I'll get 0...NumRows in the [DupeCandidateImport].[SequenceSeed] column. Then SET @maxSeed = MAX([DupeCandidateImport].[SequenceSeed] and UPDATE [IntegerSeed].[SeedValue] to [IntegerSeed].[SeedValue] + @maxSeed. Then I'll UPDATE [DupeCandidateImport].[AppSequenceNumber] to [DupeCandidateImport].[SequenceSeed] + @maxSeed.

Now I've got a street legal SequenceNumber I can use when I do my INSERT into [IPMergeRequest].

That seems a bit convoluted ( 🙂 ), but it does excercise the IDENTITY property to get the sequential numbers, and requires only a single UPDATE to the [IntegerSeed] table. Now the INSERT to [IPMergeRequest] is isolated from any further messing about with [IntegerSeed] and concerns about possible collisions with other users.