3

I have one table that holds payment data. I'd like to insert orders that ended in a declined state into #tmp_Declined. Then I'd like to insert payments that were originally declined but then authorized when using an alternative paymethod into #tmp_Authorized.

I can do this using 2 select into statements with slightly different where clauses but using the same base table.

I considered an except example like the following:

select *
into #tmp_Declined
from Payments

except

select *
from Payments 
where WasOrginallyDeclined = 1 
and MostRecentPaymentAccepted = 1 

This approach though still populates one temp table at time.Is there a reserved word or query "trick" that will insert into #tmpA where conditions met and then insert into #tmpB whatever wasn't inserted in #tmpA?

I'd like to be able to write to 2 tables with one transaction.

  • How about inserting into a single temp table with a flag denoting which of the two states the payment is? – Gareth Lyons Dec 7 '17 at 18:45
5

The feature that you're describing is sometimes called multi-table insert. Oracle is one RDBMS that implements it. It is not implemented in SQL Server.

If you need to split your data doing just a single pass of the data you have a few workarounds but all of them add complexity and have other negative side effects. You could define a partitioned heap and insert into that heap. The partitioning column would be a 0/1 computed value that defines the target table. You can partition switch out of the heap into two separate tables essentially for free. Overall this requires a sort of the data before insert.

You could define a partitioned view, insert your data into the view, and drop the view after. The requirements of a partitioned view don't seem to fit very well with your scenario. Among other things, you'd need primary keys on both target tables that contain the computed column which determines the target table. The query plans for inserting data can look pretty ugly (although every query plan looks astoundingly beautiful in SSMS 17.4):

enter image description here

A third option is to define an INSTEAD OF INSERT trigger on a target table. The trigger code can send rows to the right table. As you might imagine this has some overhead.

If the requirement is to simply insert data in a single transaction you can do that with an explicit transaction and two INSERT queries. I would just negate the WHERE clause instead of joining back to the same table. Just make sure that you handle NULLs correctly. Example code:

BEGIN TRANSACTION;

INSERT INTO #tmp_Declined
select *
from Payments 
where NOT (WasOrginallyDeclined = 1 and MostRecentPaymentAccepted = 1);

INSERT INTO #tmp_not_Declined
select *
from Payments 
where WasOrginallyDeclined = 1 and MostRecentPaymentAccepted = 1;

COMMIT TRANSACTION;
5

Is there a reserved word or query "trick" that will insert into #tmpA where conditions met and then insert into #tmpB whatever wasn't inserted in #tmpA?

Yes, it's pretty simple:

BEGIN TRANSACTION;

INSERT INTO #tmpA (...)
    SELECT...
    FROM Payments
    WHERE <some_condition> ;

INSERT INTO #tmpB (...)
    SELECT ...                 -- same as above,
    FROM Payments              -- no WHERE or a different one
    EXCEPT
    SELECT ...                 -- but don't insert
    FROM #tmpA ;               -- whatever went into tmpA

COMMIT TRANSACTION;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.