Ytukyg

I have a temporary table that contains data that needs to be inserted on two different tables, the temp is defined as:

CREATE TABLE #PaymentHistory

    (

        amount INT,

        ImportantValue INT

    )

then I have a first table called PaymentHistory which is defined as:

CREATE TABLE PaymentHistory

    (

        PaymentKey BIGINT IDENTITY,

        amount INT

    )

Finally I have a second table called SecondTable which is defined as:

CREATE TABLE SecondTable

    (

        PaymentKey INT,

        ImportantValue INT

    )

So I need to use the data on #PaymentHistory to insert on PaymentHistory, then use the identity generated on that table in the PaymentKey field to insert into SecondTable using both the PaymentKey identity and the ImportantValue on the #PaymentHistory table

So, the only table that should begin with data is #PaymentHistory, let's say I have the following records on that table:

Amount          |      ImportantValue

10              |      2

15              |      5

9               |      21

So the end result of the insert I'm after should yield the following results when I select from:

PaymentHistory:

PaymentKey      |      Amount

1               |      10

2               |      15

3               |      9

SecondTable:

PaymentKey      |      ImportantValue

1               |      2

2               |      5

3               |      21

I cannot modify the schema of PaymentHistory table, but I could alter the temporary table or the SecondTable if needed

I tried using OUTPUT to get both the identity and the ImportantValue, but since the ImportantValue is on the source table, I cannot get it with OUTPUT. I can't think of another way to link the data correctly, any help will be much appreciated

I would add a column to your #PaymentHistory before the insert... and find out what the next val would be for the first table

alter table #PaymentHistory

add ID bigint identity(1,1)



declare @i bigint = (select max(PaymentKey) from PaymentHistory)

Now do your insert, ordered...

insert into PaymentHistory

select amount 

from #PaymentHistory

order by ID

And insert into the second table

insert into SecondTable

select PaymentKey, ImportantValue 

from PaymentHistory

inner join #PaymentHistory on #PaymentHistory.ID + @i = PaymentHistory.PaymentKey

Alright, so it turns out someone from my team helped me come to an answer regarding this, I'm posting it here in case anyone stumbles upon this question

merge  PaymentHistory

using  #PaymentHistory src

on            1 = 0

when not matched then

       insert (amount) values (amount)

output inserted.PaymentKey, src.ImportantValue 

into SecondTable (PaymentKey, ImportantValue)

;