I'm working in a vendor-supplied application that has many kinds of sequence numbers. The generic type 'SequenceNumber' is used in many different tables to provide a 'tiebreaker'.
The target table is:
/****** Object: Table [dbo].[IPMergeRequest] Script Date: 6/25/2014 5:02:24 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[IPMergeRequest](
[IPCode1] [int] NOT NULL,
[IPCode2] [int] NOT NULL,
[RequestDate] [datetime] NULL,
[SequenceNumber] [int] NOT NULL,
[ProcessDate] [datetime] NULL,
[ProcessCode] [int] NULL,
[CommentText] [nvarchar](max) NULL,
[OperatorID] [nvarchar](255) NULL,
[UpdateDate] [datetime] NULL,
CONSTRAINT [PK_IPMergeRequest] PRIMARY KEY CLUSTERED
(
[IPCode1] ASC,
[IPCode2] ASC,
[SequenceNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
My table of candidates is:
/****** Object: Table [dedupe].[DupeCandidateImport] Script Date: 6/25/2014 5:06:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dedupe].[DupeCandidateImport](
[Confidence] [float] NULL,
[SurvivingIpCode] [nvarchar](255) NULL,
[SurvivingLastName] [nvarchar](255) NULL,
[SurivingFirstName] [nvarchar](255) NULL,
[SurvivingPreferredName] [nvarchar](255) NULL,
[SurvivingValue] [nvarchar](255) NULL,
[SurvivingBirthDate] [datetime] NULL,
[NonSurvivingIpCode] [nvarchar](255) NULL,
[NonSurvivingLastName] [nvarchar](255) NULL,
[NonSurvivingFirstName] [nvarchar](255) NULL,
[NonSurvivingPreferredName] [nvarchar](255) NULL,
[NonSurvivingValue] [nvarchar](255) NULL,
[NonSurvivingBirthDate] [datetime] NULL,
/*About 150 flags that are part of the dedupe logic*/
) ON [PRIMARY]
GO
and the vendor's seed table is:
/****** Object: Table [dbo].[IntegerSeed] Script Date: 6/25/2014 5:09:32 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[IntegerSeed](
[SeedType] [nvarchar](255) NOT NULL,
[SeedValue] [int] NULL,
CONSTRAINT [PK__IntegerSeed__10566F31] PRIMARY KEY CLUSTERED
(
[SeedType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
I've been asked to take a list of merge candidate values, import them into [DupeCandidateImport], and provide an interface through which users can help determine which pairs should actually e merged and make corrections to allow ones that are initially disqualified such that they can e merged. Based on those edits and the ConfidenceLevel the user selects, I insert them in [IPMergeRequest], but in order to do that I need to generate a range of sequence numbers in agreement with the vendor's sequence numbers.
The vendor's sequence number table is contains the current last used value for each type.
In order to insert the imported candidates into merge request table I have to have a street legal sequence number for each one. There are anything from 0 to thousands of rows already in [IPMergeRequest], each having a legal SequenceNumber.
Other processes (part of the vendor's application) also insert values into [IPMergeRequest], but not as part of a batch process. Tables other than [IPMergeRequest} use sequence numbers from the same SeedType that I can't collide with.
If this were greenfield I'd certainly not do it this way, but I'm working within existing code constraints, so I think this is the way I need to do it....