Sql server looping thru each records and editing

Question

0.00/5 (No votes)

See more:

How do i write SQL code that's equivalent to vba code (below). Thanks in advance for any assistance on this. I haven't done much SQL coding.

I've written this code in MS Access to edit records in a single table. The two recordset is used to to test if there is a change in the values of J_CaseNum or J_Index.

For each combination the J_PersonIndex needs to be incremented starting at 1. There can be anywhere from 1 to 70 person for a given combination, 160,000 records in the table.

Once the code is ran the results should look like this:

CaseNum    Index  Name   PersonIndex
1001       001    Allen  01
1001       001    Brown  02
1001       001    Smith  03
1001       002    Alfred 01
1002       001    Billy  01
1002       001    Lima   02

Here's the VBA code

VB

sql = "Select J_CaseNum, J_Index, J_VehicleIndex, J_PersonIndex, J_CaseNo, J_PersonID, Ind_Last_NM  from dbo_DCIPS Order By J_CaseNum, J_Index, Ind_Last_NM"
Set rs = CurrentDb.OpenRecordset(sql)
Set rs1 = CurrentDb.OpenRecordset(sql)

rs.MoveLast
rs.MoveFirst
i = 0

While Not rs.EOF
    If i = 0 Then
        rs.Edit
        rs!J_PersonIndex = "01"
        rs!J_CaseNo = rs!J_CaseNum & rs!J_Index
        rs!J_PersonID = rs!J_CaseNo & rs!J_VehicleIndex & rs!J_PersonIndex
        rs.Update
        j = 1
        i = 1
        rs.MoveNext
    Else
        If (rs1!J_CaseNum = rs!J_CaseNum) And (rs1!J_Index = rs!J_Index) Then
            j = j + 1
            s = j
            rs.Edit
            rs!J_PersonIndex = Right("00" + s, 2)
            rs!J_CaseNo = rs!J_CaseNum & rs!J_Index
            rs!J_PersonID = rs!J_CaseNo & rs!J_VehicleIndex & rs!J_PersonIndex
            rs.Update
            rs.MoveNext
            rs1.MoveNext
        Else
            j = 1
            s = j
            rs.Edit
            rs!J_PersonIndex = Right("00" + s, 2)
            rs!J_CaseNo = rs!J_CaseNum & rs!J_Index
            rs!J_PersonID = rs!J_CaseNo & rs!J_VehicleIndex & rs!J_PersonIndex
            rs.Update
            rs.MoveNext
            rs1.MoveNext
        End If
    End If
    i = i + 1
Wend

What I have tried:

The VBA code runs fine, took 7hours to finish.

Posted 30-Dec-16 11:15am

nordyck

Updated 30-Dec-16 13:49pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Narud Shiro · Accepted Answer · 2016-12-30T13:49:00

Since I haven't the table structure, and no data to test, I'm not absolutely sure about the result. But this should have to work if you have at least SQL Server 2005 as RDBMS.

SQL

WITH CTE_DCIPS
AS (
SELECT ROW_NUMBER() OVER(PARTITION BY J_CaseNum, J_Index ORDER BY Ind_Last_NM) RowNum 
    , J_CaseNum
    , J_Index
    , J_VehicleIndex
    , J_PersonIndex
    , J_CaseNo
    , J_PersonID
    , Ind_Last_NM  
FROM dbo_DCIPS 
)

UPDATE tbl
SET tbl.J_PersonIndex = RIGHT('00' + CONVERT(varchar(5), cte.RowNum), 2)
    , tbl.J_CaseNo = tbl.J_CaseNum + tbl.J_Index
    , tbl.J_PersonID = tbl.J_CaseNo + tbl.J_VehicleIndex + RIGHT('00' + CONVERT(varchar(5), cte.RowNum), 2)
FROM dbo_DCIPS tbl 
INNER JOIN CTE_DCIPS cte
ON tbl.J_CaseNum = cte.J_CaseNum AND tbl.J_Index = cte.J_Index