Insert rows that match a condition, and return rows that don't

Question

I am using Sql Server 2014

I have an expensive query. I want to insert the results of that query into a table.

However the results should match a certain condition, if they don't I don't want to insert them, but I do want to return them so that my application can warn/record these violations.

So essentially I want to run both of the following:

INSERT INTO MyTable
SELECT ExpensiveQuery
WHERE MyCondition;

SELECT ExpensiveQuery
WHERE Not MyCondition;

But I don't want to run the expensive query twice.

Is this possible?

Answer 1

You might store the records of the expensive query into a tempdb table and you can use that with your condition on the tempdb, this way you won't need to run your expensive query twice.

select * into tempdb..expensive_query from Userdb.dbo.expensive_query

Afterwards, you may use your conditional statement as below:

1st query will look like:

Insert into MyTable SELECT from tempdb..expensive_query
WHERE MyCondition;

2nd query will be:

SELECT from tempdb..expensive_query
WHERE Not MyCondition;

You may use # or ##table also instead of tempdb.dbo.table_name, this depends on whether you are doing that in the same session or multiple database sessions are required however tempdbb.dbo.table_name is safer.

You might use CTE also.

Hope above helps.