Sql-server – Using T-SQL, is it possible to a split a result set or table and then insert into two different temp tables

exceptsql servert-sql

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.

Best Answer

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):

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;

Related Solutions

Sql-server – How to use running total aggregates in a query to output financial accumulations

Interesting problem. Here is my attempt.

First we calculate a running total of the Allocation.InvoiceAmt over the various InvoiceId:

WITH inv AS
  ( SELECT 
      InvoiceId, Account, 
      InvAmt = InvoiceAmt,
      SumInvAmt = SUM(InvoiceAmt) OVER 
        (PARTITION BY InvoiceId 
         ORDER BY Account
         ROWS BETWEEN UNBOUNDED PRECEDING
                  AND CURRENT ROW)
    FROM Allocation 
  )

and the same for Payment.Amount:

, pay AS
  ( SELECT 
      PaymentId, InvoiceId, PaymentType, CheckNumber, 
      PayAmt = Amount,
      SumPayAmt = SUM(Amount) OVER 
        (PARTITION BY InvoiceId 
         ORDER BY PaymentId
         ROWS BETWEEN UNBOUNDED PRECEDING
                  AND CURRENT ROW)
    FROM Payment 
  )

Then we combine the previous two CTEs:

SELECT 
    inv.InvoiceId,
    pay.PaymentId,
    inv.Account,
    PaymentAllocated = 
      CASE WHEN SumPayAmt <= SumInvAmt - InvAmt
             OR SumInvAmt <= SumPayAmt - PayAmt
      THEN 0
      ELSE
          CASE WHEN SumPayAmt <= SumInvAmt THEN SumPayAmt      -- these would be
               ELSE SumInvAmt END                              -- simpler if there
        - CASE WHEN SumPayAmt-PayAmt <= SumInvAmt-InvAmt       -- was a LEAST() and 
               THEN SumInvAmt-InvAmt                           -- a GREATEST() function
               ELSE SumPayAmt-PayAmt END
      END
FROM inv JOIN pay
  ON inv.InvoiceId = pay.InvoiceId
ORDER BY 
    inv.InvoiceId,
    pay.PaymentId,
    inv.Account ;

Test at SQL-Fiddle

Sql-server – T-SQL View — How to ‘pre-fetch’ schema using scalar function, then populate using table query

I'm wondering if these views could be re-written using CTEs, to somehow 'pre-fetch' the modified/pre-fixed schemas using the functions, and then query the tables with SELECT INTO to populate data into the view.

No, this isn't possible with a view. You could do something broadly along these lines with a multi-statement function (MSUDF), but:

This would still require a static schema definition for the table variable; and
Careful design would be required to avoid terrible performance; and
All potential base query predicates must be supplied as optional parameters

A multi-statement function would potentially materialize the entire 'view' result set in a table variable, before any predicates in the base query were applied. If the result is large, this overhead will likely be prohibitive.

You could push predicates into the MSUDF using parameters, but then the function body becomes a mess of conditional predicates of the form column = @value OR @value IS NULL, which would require OPTION (RECOMPILE) in the MSUDF to optimize well.

Note that even with the best possible outcome, the users of the view will have to change their queries from view references to MSUDF references (with all parameters specified, DEFAULT or NULL passed for those that are not needed). This may not be workable.

All that said, you need to be certain what is causing the performance problem in the first place. The question does not supply an example of a problematic query plan, so some of what follows is educated guesswork:

There are two features of the current scheme that leap out at me:

The use of ISNULL and calculated columns prevents the optimizer from pushing base-query predicates down into the underlying in-line functions and base tables. You might get better results for the ISNULL part by rewriting the LEFT JOIN as a UNION ALL of an inner join and an anti-semi-join of the two tables. Properly written, this would expose only base column names, allowing the optimizer to push predicates successfully.
The in-line functions use an MSUDF to split the supplied CSV into row values. This is a pattern that often causes optimization problems, because the size and distribution of the result is unknown. A query plan that is optimal for a CSV of '1,2,3' is likely not optimal for a NULL CSV. Again OPTION (RECOMPILE) could help with this. It will simplify the compiled logic for the NULL case, avoid parameter-sniffing issues, as well as providing cardinality information for the MSUDF result. Distribution statistics will still not be available though.

Best Answer

Related Solutions

Sql-server – How to use running total aggregates in a query to output financial accumulations

Sql-server – T-SQL View — How to ‘pre-fetch’ schema using scalar function, then populate using table query

Related Question