Solved

On Insert Sequence Line Number in SQL One Line at a Time

Posted on 2009-04-10
13
310 Views
Last Modified: 2012-05-06
I need a function that will give me the ability to sequence line numbers within a record set when records are inserted and or deleted.

I have two tables a header table and a detailed table.  Within the detailed table I have the standard indexed ID field and a second ID field named AAID.  The AAID captures the ID of the header row through a session id assigned on the insert of the first record.

The detailed records that match the header id can only have a maximum of 50 Line Numbers inserted.  So for each record the AAID is unique and which indicated a new sequencing of records.

Also if a user deletes a record, say number 5, then we need to re-sequence.

I hope that this is clear and not to fuzzy.

Let me know.
Thanks
0
Comment
Question by:gagnonmv
  • 6
  • 6
13 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24118824
When you say "Also if a user deletes a record, say number 5, then we need to re-sequence.", are you talking about the Header Row or the Detail Row?

Are you will/able to use a stored procedure to do your requesence step?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24119056
I think that you can probably leverage the row_number() function.

The syntax would be:

row_number() over (partition by PK_FROM_HEADER_RECORD order by WHATTOORDERBY)
0
 

Author Comment

by:gagnonmv
ID: 24119138
With the sequencing we are only talking about the detailed table, not the header
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24119208
If there is something other than the AAID that lets you order the Detail rows, Brandon's approach could work nicely.  If, on the other hand, there is not something other than the AAID that lets you order the rows properly, then you will have to do some manipulating to get things to work right.
Have you considered adding an Identity column in the Details table so that you can maintain the order based on that and then displaying the AAID based on something like Brandon's approach.  You could still maintain the AAID and resequence it (if that is a requirement of some sort) but, by having the Identity column, you would be able to run a query that updates the AAID column from the ROWNUMBER() by selecting the data ordered by the ID and Identity columns.
0
 

Author Comment

by:gagnonmv
ID: 24119379
I do have an I'd field inaddtion to the AAID column.                                              
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24119777
In that case, use the ROWNUMBER approach, selecting the data for the given ID from the Header row and the identity column to maintain the order of the rows within the Details.
I would start by setting up a SELECT query that pulls the Header ID, the Detail Identity column, the current AAID, and the ROWNUMBER calculation for the NewAAID.  That will let you establish that you are getting a good calculation of the new AAID.  THen you can use that to feed the UPDATE query so that you can join on the Header ID and the Detail Identity column and UPDATE the AAID column to the ROWNUMBER.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:gagnonmv
ID: 24130035
So this is the Select Query with the Row Number which works fine.

SELECT ID, AAID, ROW_NUMBER() OVER(PARTITION BY AAID ORDER BY ID) AS 'Row_Number'
FROM [ABC].[dbo].[AA_DETAIL]

So how do I write the update query to incorporate this code.

Thanks
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24130098
It is fairly simple in SS2005.  
If the query you provided gives you a good line new number with which to update the AAID, then try the following:

UPDATE T

SET    AAID = RowNumber

FROM   AA_DETAIL T

INNER JOIN

(

SELECT ID, 

       AAID, 

       ROW_NUMBER() OVER(PARTITION BY AAID ORDER BY ID)

       AS 'Row_Number' 

FROM [ABC].[dbo].[AA_DETAIL]

) Z

ON T.ID = Z.ID

Open in new window

0
 

Author Comment

by:gagnonmv
ID: 24130231
What goes in the place of T and Z, I assume Table 1 and table 2? and I keep getting an incorrect sytax on line 11 ')'

Thanks
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24130308
T and Z are aliases and NOTHING should be used to replace them.  In other words, you should not have to modify that code at all.  If you put the table names in those places, that would explain the error on line 11.
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 500 total points
ID: 24130313
You may want to get a couple of books on T-SQL and study them. ;-)  I can highly recommend "The Guru's Guide to Transact-SQL" and "SSQL Cookbook".
0
 

Accepted Solution

by:
gagnonmv earned 0 total points
ID: 24130568
So here is the code, but it doesn't update the records:

UPDATE T
SET    AAID = RowNumber
FROM   [ABC].[dbo].[AA_DETAIL] T
INNER JOIN
(
SELECT ID,
       AAID,
       ROW_NUMBER() OVER(PARTITION BY AAID ORDER BY ID)
       AS 'RowNumber'
FROM [ABC].[dbo].[AA_DETAIL]
) Z
ON T.ID = Z.ID
0
 

Author Comment

by:gagnonmv
ID: 24130912
Sorry my mistake, I have gotten this to work correctly.  Thanks for the help
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to set audit trial on sql server 4 45
sql query help 7 96
SQL Server Remove all trailing commas 10 71
Grid querry results 41 72
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now