SQL Insertion Question

[Former Member]

Given that Max(CODE_ID) = 1006 and CODE_ID is the primary key and that....

begin

declare varCodeID integer;

select Max(CODE_ID) + 1 into varCodeID from CODE;

-- Insert Parent Code

insert into CODE(CODE_ID,PARENT_CODE_ID,CODE_NAME)

select varCodeID,1,'Example Parent';

-- snipped insertion of children

end

works as expected, why does the following fail saying that 1007 is a duplicate primary key?

begin

declare varCodeID integer;

select Max(CODE_ID) + 1 into varCodeID from CODE;

-- Insert Parent Code

insert into CODE(CODE_ID,PARENT_CODE_ID,CODE_NAME)

select varCodeID,1,'Example Parent' from CODE;

-- snipped insertion of children

end

Hoping the answer isn't too embarrassing, Paul

[regdomaratzki · Accepted solution]

I'm pretty sure that your second example will attempt to insert a row with primary key 1007 into the CODE table for each row that already exists in the table.  If there are multiple rows in the table already, I would expect a primary key violation.

Reg

[Former Member · Accepted solution]

Of course! Thanks Reg