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

Question

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

Navigation

#1 by (1 votes)

3

My question is this: I have my cursor for the interaction line by line 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 +1 in while , however it's popping 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

The following is 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).

sql sql-server tsql

asked by anonymous 25.08.2015 / 20:21

1 answer

Summarize dynamic PHP texts with jquery Is there any way to convert text to image without using pictureBox in vb6?

score 1 · Answer 1

The problem is occurring because the @MaxId of PROPOSAL is obtained and, without incrementing it, an INSERT is made in PROPOSAL. The increment is done only at the end of the loop. Because 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);

So:

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

Do the same for @test:

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