SQL Loop through table, get max number and increment at insert

Question

I have two tables, and I'm looping though table1 to see if any records from table2 exists in there. If the table1.IMAGE_NUMBER already exists for that person, add max(table2.IMAGE_NUMBER) + table1.IMAGE_NUMBER before inserting a new record, otherwise just insert without incrementing the IMAGE_NUMBER.

The goal is to not have duplicate IMAGE_NUMBER Here is my code:

DECLARE @NAME NVARCHAR(50), @ID INT, @IMAGE_NUMBER INT, AGE INT 
DECLARE MY_CURSOR CURSOR FOR SELECT NAME, IMAGE_NUMBER, AGE FROM PEOPLE

OPEN MY_CURSOR   
FETCH NEXT FROM MY_CURSOR INTO @NAME, @ID, @IMAGE_NUMBER, @AGE 

WHILE @@FETCH_STATUS = 0   
BEGIN
    
    IF NOT EXISTS(SELECT R.NAME, RG.ID, R.IMAGE_NUMBER, R.DOB, R.PHONE
        FROM REGISTRATION R
        LEFT JOIN PEOPLE P ON R.ID = P.ID
        WHERE P.NAME = R._NAME
        AND P.IMAGE_NUMBER = R.IMAGE_NUMBER
        AND P.IS_ACTIVE = 1)

    BEGIN 
        -- how do I increment the image_number + max(R.IMAGE_NUMBER) from registration???
        INSERT INTO REGISTRATION(NAME, IMAGE_NUMBER, DOB, AGE)
        VALUES(@NAME, @IMAGE_NUMBER, @DOB, @AGE)
        
    END
    
    ELSE IF EXIST(SELECT R.NAME, RG.ID, R.IMAGE_NUMBER, R.DOB, R.PHONE
        FROM REGISTRATION R
        LEFT JOIN PEOPLE P ON R.ID = P.ID
        WHERE P.NAME = R._NAME
        AND P.IMAGE_NUMBER = R.IMAGE_NUMBER
        AND P.IS_ACTIVE = 1)
        
    BEGIN
    
     UPDATE REGISTRATION SET DATE_PROCESSED=GETUTCDATE()
     
    END

    FETCH NEXT FROM MY_CURSOR INTO @NAME, @ID, @IMAGE_NUMBER, @AGE 
END   
CLOSE MY_CURSOR   
DEALLOCATE MY_CURSOR 

Answer 1

Ideally you wouldn't bother storing an actual number per each NAME. Just use ROW_NUMBER to calculate a numbering when you query it.

---

Be that as it may, there are quite a few issues with your current code:

• There is absolutely no need for a cursor (there rarely is), you can do this in one batch operation.
• Even if you wanted a cursor there are major bugs which would prevent your code from working.
• Your first EXISTS isn't actually comparing each row, it's comparing all rows, so it wouldn't work anyway.
• LEFT JOIN followed by WHERE on the same table converts it to an INNER JOIN logically.
• Unclear why you need both DOB and AGE, in fact I don't know why you would store age at all given you can calculate it from the DOB.
• Unclear why you have a separate UPDATE statement

I'm not exactly sure of your table schema, but it seems you want something like this.

INSERT INTO REGISTRATION (NAME, IMAGE_NUMBER, DOB, AGE, DATE)
SELECT
  P.NAME,
  ISNULL(R.MAX_NUMBER + 1, P.IMAGE_NUMBER),
  P.DOB,
  P.AGE,
  GETUTCDATE()
FROM PEOPLE P
LEFT JOIN (    -- pre-aggregate existing images and get the max number
    SELECT MAX(R.IMAGE_NUMBER) MAX_NUMBER
    FROM REGISTRATION R
    GROUP BY R.NAME
) R ON P.NAME = R._NAME
WHERE P.IS_ACTIVE = 1;