>I don't want to be picky here, but that is exactly the reason why you should not use auto incremental columns. For these kind of reasons (and it can be even worse where you want to insert a whole batch of them in one transaction) you should have an oldfashioned key generator stored procedure which can generated keys and batches of keys for you and them to be known for your client application.
>
>Many database experts agree upon that the autoincremental keys are a very bad implementation and seduces developper to use them and find themselves in very difficult situations where they cannot solve these kind of problems.
>
>If you can I would do the following:
>1. Create a Getnewkey stored procedure
>2. In the insert trigger of each table you check if the key has been defined. If not you call the GetnewKey Procedure. This would solve you inmediate problem, but ensures that existing code does not break.
>3. For each troublesome case where you have to know the PK at the client side, call the getnewkey procedure directly.
I have been doing that for years. I also trained several developers on those techniques. However, from a .NET environment, using those data providers, and going towards SQL Server, it was time to start using auto incremental keys. I also found that all my clients have switched to that now. So, it is easier for now to adapt the general approach in order to accomodate most of their needs.