I have a table that uses an identity column to auto-generate a primary key.

The problem is that when I do an insert, I need to insert the number into two different tables.

So my question is, how do I capture the value auto-generated in the insert to the first table in order to insert it into the second table?

Assume the Employee table has a PK of empno, which is an integer defined as a identity column.

select empno from final table (insert into employee (empno, name, salary) values (default, 'Smith', 75000));

I understand what you are doing in the insert, but I don't understand the whole thing.

I assume "final table" is the second table I will be inserting into. However, how can I select a value that doesn't exist? I also don't see where you insert the auto-generated number into the second table.

Can you provide a few more details on your example?

No, final table is not a real table, it is the contents of the employee table after the data is inserted. Instead of just asking a bunch of questions, try the statement I gave you (on a real table). If you have any specific questions about how final table works, check out the manual.