Inserted records with a subsequence number

Question

Inserted records with a subsequence number

mcgillivrayk

SSC Enthusiast

Points: 196
More actions
June 14, 2009 at 6:45 am

#138602

I am looking for a way to insert records into a child table that has a primary key from a parent table and a sequence number that increments for each additional record associated with the parent.
For instance, I have a claim with mulitple lines
Claim Id Line sequence Description
1 1 this is for claim 1 line 1 information
1 2 this is for claim 1 line 2 information
1 3 this is for claim 1 line 3 information
2 1 this is for claim 2 line 1 information
2 2 this is for claim 2 line 2 information
We are using SQL Server 2005.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29423 More actions · Answer 1

mcgillivrayk (6/14/2009)
I am looking for a way to insert records into a child table that has a primary key from a parent table and a sequence number that increments for each additional record associated with the parent.

Use Identity column for the sequence number.

http://msdn.microsoft.com/en-us/library/aa933196.aspx

Pradeep Singh

Lowell SSC Guru Points: 323546 More actions · Answer 2

An Identity column will not meet your requirement, since you need it to reset back to 1 with each claim number.

as you've probably already found, there's no inbuilt way to do that.

there's three ways to solve this that I can think of off hand; it's a somewhat common requirement.

In my opinion, the best way is to not include the Line Sequence in the table, and only generate it on demand by using the row_number() over(partition by Order By) method. you could also simply create a view with that query as well.

the second and third method still would use row_number, but you'd use a trigger to populate the Line Sequence, or a user function to populate the Line Sequence;

both would actually store the value in the table itself; unless you use an INSTEAD OF trigger,it might be possible to unwittingly update the sequence when it shouldn't.

here's how i would do the first method, with a view.

Create Table ClaimTable(

ClaimID int Identity(1,1) Primary key,

ClaimText varchar(30) )

Create Table ChildTable(

ChildId int Identity(1,1) Primary key,

ClaimId int References ClaimTable(ClaimID),

LineSequence int,

Description varchar(1000) )

insert into ClaimTable (ClaimText)

SELECT 'Claim 1' UNION ALL SELECT 'Claim 2' UNION ALL SELECT 'Claim 3'

insert into ChildTable(ClaimID,Description)

SELECT 1,'this is for claim 1 line 1 information' UNION ALL

SELECT 1,'this is for claim 1 line 2 information' UNION ALL

SELECT 1,'this is for claim 1 line 3 information' UNION ALL

SELECT 2,'this is for claim 2 line 1 information' UNION ALL

SELECT 2,'this is for claim 2 line 2 information'

--the sql to use as a view

Select

ClaimTable.ClaimID,

ClaimTable.ClaimText,

ChildTable.ClaimId,

row_number() OVER (PARTITION BY ChildTable.ClaimId ORDER BY ChildTable.ClaimId) AS LineSequence,

ChildTable.Description

FROM ClaimTable

INNER JOIN ChildTable ON ClaimTable.ClaimID = ChildTable.ClaimID

--results:

ClaimID ClaimText ClaimId LineSequence Description

------- --------- ------- ------------ ---------------------------------------

1 Claim 1 1 1 this is for claim 1 line 1 information

1 Claim 1 1 2 this is for claim 1 line 2 information

1 Claim 1 1 3 this is for claim 1 line 3 information

2 Claim 2 2 1 this is for claim 2 line 1 information

2 Claim 2 2 2 this is for claim 2 line 2 information

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mcgillivrayk SSC Enthusiast Points: 196 More actions · Answer 3

Thansk for the ideas.

Here is a follow up on the use of the Child Identity column.

Wouldn't this result in the related records to the same claim be on separate pages since the parent claim id is not part of the clustered index?

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

Yes. Of course, you can change that. I don't believe the method that Lowell posted is dependent on what the primary key actually is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)