Store identity before and after an insert?

Is there any way to store the identity values just prior to an insert and the new identity values just after an insert into a mapping table? The mapping table will be used to update foreign keys in other tables.

Initially I thought this was possible with the OUTPUT INTO cla开发者_C百科use but only the inserted values are insertable. I'm not sure how to use the OUTPUT clause all by itself to do the job. Maybe some sort of subquery?

Maybe this is all too complicated and there is a way to use SCOPE_IDENTITY and subqueries to do this.

Any help would be much appreciated.

p.s. Yeah, I know I can solve this by 'reserving' identities (insert dummy rows and then delete or DBCC CHECKINDENT), save the newly 'reserved' identities and the old identities into the mapping table, update the table with the new identities and finally doing an insert with INSERT_IDENTITIES turned on - but that is messy.

This applies to SQLServer 2008R2.

Here is what doesn't work:

INSERT 
    [SomeTable]
    OUTPUT
           INSERTED.[Id],
           [Id] -- This doesn't work
        INTO
           [#mappingTable]
SELECT
    (SomeColumns)
FROM
    [#someOtherTable];

---

You can't know in advance what the identities will be.

And the OUTPUT clause only works on the table you have inserted into, not the source tables. I've wished for this too: would be useful.

So, you can only re-query the INSERTed table (like below) or to UPDATE #mappingTable

INSERT 
    [SomeTable]
SELECT
    (SomeColumns)
FROM
    [#someOtherTable];

SELECT
    ID, stuff
FROM
    SomeTable

---

It can be done with a MERGE statment and an OUTPUT clause. Too bad no one answered with this info, my coworker showed me though.