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:
INSERT INTO #tmp_Declined
where NOT (WasOrginallyDeclined = 1 and MostRecentPaymentAccepted = 1);
INSERT INTO #tmp_not_Declined
where WasOrginallyDeclined = 1 and MostRecentPaymentAccepted = 1;