1

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

Santiago San Martin
  • 84
  • 10

2 Answers2

1

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
S3S
  • 24,809
  • 5
  • 26
  • 45
  • Feels a bit like a hack, but it's a lot better than what I was coming up with!! If there is no better answer tomorrow morning, I'll accept it with the green tick – Santiago San Martin Nov 20 '18 at 20:43
  • 2
    `MAX(Column)` is not necessarily the next identity value for the column (and worse, it's `NULL` if the table is empty); that's `IDENT_CURRENT('Table')`. Even then all of this needs to be wrapped in a transaction to have any hope of correctness in the face of concurrency. – Jeroen Mostert Nov 20 '18 at 20:43
  • 1
    I didn't expect `NULL` which would break if it was a new table for sure. In that case, this wouldn't even be a problem. Why wouldn't it be the next (+1) based on the identity increment @JeroenMostert? Even if rows were deleted. Fair point on the transactions, but I'd do a lot of error handling too that i didn't include here. – S3S Nov 20 '18 at 20:44
  • 1
    `BEGIN TRANSACTION; INSERT ...; ROLLBACK`. Voila, now your identity is *higher* than the max ID present. – Jeroen Mostert Nov 20 '18 at 20:45
  • I see, and assumed they'd set xact_abort on so one insert couldn't happen if the other failed (was rolled back) – S3S Nov 20 '18 at 20:46
  • Once you start being very reliant on the identity across multiple tables, it starts making more sense to replace it with a sequence instead; controlling those in a transaction is more intuitive and allows you to insert into tables in the order that's convenient, rather than based on what's producing identities. Of course, sometimes you have to make do with what you've got. – Jeroen Mostert Nov 20 '18 at 20:49
  • Yes, I'm afraid the client won't allow changes on the PaymentHistory table, I can only modify the temporary table or the SecondTable if needed – Santiago San Martin Nov 20 '18 at 20:51
0

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)
;
Santiago San Martin
  • 84
  • 10