sql – Get the max value inside a while that is inside a cursor

Question:

My question is: I have my cursor for line-by-line interaction and inside it I have a while for horizontal interaction. I'm trying to populate a table whose PK does not have identity , using MAX and setting the value +1 inside the while , however there is an error stating that the inserted 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

Here's 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:

The problem is occurring, as the @MaxId of PROPADESAO is obtained and, without incrementing it, an INSERT is made in PROPADESAO. The increment is done only at the end of the loop. Since the 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;
Scroll to Top
AllEscort