June 25, 2014 at 4:08 pm
Given a Table1 with two columns 'Name' with some N rows of data and another Table2 with one column 'SeqNo' with N rows, each of which contains a unique integer which can be ordered monotonically, I want to do an INSERT into some Table3 with two columns 'Name' and 'SeqNo' such that each INSERT'd row gets one of the unique integers.
e.g. -
Table1 contains 'Fred','Tom','Mary','Larry'
Table2 contains 6000978,6000979,6000980,6000981
INSERT INTO Table3
SELECT Table1.Name
Table2.SeqNo
FROM Table1
And I want to get Table3
'Fred',6000978
'Tom'.6000979
'Mary',6000980
'Larry',6000981
How can I reference Table2 so that Table2.SeqNo will 'line up' properly? Note that the ordering of the SeqNo values isn't mandatory as long as each SeqNo is assigned to one and only one row.
On edit: Table2 isn't required, it's just the way I started thinking about it. It would be nicer to just have two integer vars, @StartSeqNo = 6000978 and @EndSeqNo = 6000981 for he example above. Either way is fine.
June 25, 2014 at 4:22 pm
Is this a simplified version of a much larger problem where the values in Table2 need to be pre-generated?
The reason I ask is because you're re-inventing the wheel called IDENTITY() and I'm not understanding why you'd want to do that. The new SEQUENCE would also perform a similar idea and allow you to grab ranges as long as you're in 2012+.
http://msdn.microsoft.com/en-us/library/ff878091(v=sql.110).aspx
Can you help us understand the need for this?
However, I wouldn't approach this directly the way you are. I'd use a one to one relationship off the surrogate key in your table1 (the identity or whatever), and then assign that to a field in table2 to make sure that you don't end up with concurrency issues.
The reason I'd go that way instead of bringing the key to table1 is because it's less data passes, thus reducing the chance for concurrent pickup by multiple users. By forcing the update to occur on the table you need to make sure doesn't duplicate, and you are doing this as the rows insert into table1, multiple inserts can't update the same row, but they could have read the same row and wrote the user in with duplicated #s.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 25, 2014 at 5:20 pm
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....
June 25, 2014 at 8:45 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply