>> maybe I'm missing something?

Nope. You're not missing anything. You've got the tail wagging the dog. You need to get the record created so that you can get the IDENTITY value, but you can't get the record created because the PRIMARY KEY constraint is validated before the record is created.

Something's going to have to give.

He's my suggestion: make the PK an IDENTITY column and use the other value that you're trying to create a candidate key.

-Mike

>Thanks for your response Alexandre,
>
>The problem is that cCode_id is a primary key so when it tries to insert it will fail b/c it requires a value, or maybe I'm missing something?