How to do one where BEFORE multiple inserts in SQL Server

Question

I'm doing a multiple insert in SQL Server using UNION ALL between the inserts. In the last part of the query I have a WHERE clause. Now it seems that that the WHERE clause is executed before every statement, but I only want the WHERE to be executed one time. If the WHERE clause has a result then none of the inserts should be executed.

For illustration, insert some persons into a table, if any records exists with one of the defined ages none of the inserts should be executed.

INSERT INTO mytable 
    select 1, 33,john UNION ALL
    select 2, 28,james UNION ALL
    select 3, 20,Harry UNION ALL
WHERE NOT EXISTS (SELECT 1 FROM mytable where age in(22,28,30))

How should I do this?

Answer 1

Try this instead:

INSERT INTO mytable 
(id, age, name)
SELECT * FROM
(
    SELECT 1 AS id, 33 AS age, 'john' AS name
    UNION ALL
    SELECT 2, 28, 'james'
    UNION ALL
    SELECT 3, 20, 'Harry' 
) T1
WHERE NOT EXISTS (SELECT 1 FROM mytable WHERE age IN (22, 28, 30))