Catch max value inside a while that is inside a cursor

Question

My doubt is the following: I have my cursor for the interaction line by line and within it I have one while for horizontal interaction. I am trying to popular a table whose PK does not have identity, using the MAX and setting the value +1 within the while, however is crashing error stating that the entered value already exists in the table.

DECLARE @RANGE_DE   INT,
            @RANGE_ATE  INT
    declare @MaxId int;
    declare @teste int;
    DECLARE C_REGISTROS CURSOR FOR
        SELECT 
            RANGE_DE,
            RANGE_ATE
        FROM
            #TEMPPARAMETROS

        OPEN C_REGISTROS
        FETCH NEXT FROM C_REGISTROS INTO @RANGE_DE, @RANGE_ATE

        WHILE (@@FETCH_STATUS = 0)
        BEGIN
        set @teste = (select max(nroproposta) from propadesao);
        set @MaxId = (SELECT MAX(IdPropAdesao) FROM PROPADESAO);
                    while (@RANGE_DE <= @RANGE_ATE)                     
                    begin                                               
                    INSERT INTO  
                    PROPADESAO  
                     (  
                          IdPropAdesao
                         ,IdTpProduto
                         ,IdUnidade  
                         ,DtStatus 
                         ,IdCorretor  
                         ,NroProposta  
                         ,StatusAtual  
                         ,IdMatDivulga                                
                     )
                    SELECT  
                         @MaxId
                         ,IdTpProduto                                     
                         ,1  
                         ,GETDATE()
                         ,IDCORRETOR  
                         ,@teste
                         ,CASE 
                            WHEN IDCORRETOR is null THEN 2
                            WHEN IDCORRETOR IS NOT NULL THEN 3
                          END  
                         ,idcontrato                         
                    FROM  
                         #TEMPPARAMETROS

                        set @range_De += 1
                        SET @MaxId += 1
                        set @teste += 1
                    end
                FETCH NEXT FROM C_REGISTROS INTO @RANGE_DE, @RANGE_ATE
        END
    CLOSE C_REGISTROS
    DEALLOCATE C_REGISTROS

Follow the error :

Msg 2627, Level 14, State 1, Line 21 Violation of PRIMARY KEY constraint 'PK_PropAdesao'. Cannot insert duplicate key in object 'dbo.PROPADESAO'. The duplicate key value is (5654054). The statement has been terminated. Msg 2627, Level 14, State 1, Line 21 Violation of PRIMARY KEY constraint 'PK_PropAdesao'. Cannot insert duplicate key in object 'dbo.PROPADESAO'. The duplicate key value is (5654055).

Answer 1

The problem is occurring as the @Maxid of PROPADESAO is obtained and, without increasing it, an INSERT is made in PROPADESAO. The increment is done only at the end of the loop. As INSERT is done before the end of the loop, then SQL Server raises a duplicate primary key Exception.

Change this:

set @MaxId = (SELECT MAX(IdPropAdesao) FROM PROPADESAO);

That’s why:

set @MaxId = (SELECT MAX(IdPropAdesao) FROM PROPADESAO) + 1;

Do the same for @test:

set @teste = (select max(nroproposta) from propadesao) + 1;