How to do this if before multiple inserts in SQL Server

Question

I am doing multiple insertion in SQL Server using UNION ALL between insertions. In the last part of the request, I have a WHERE clause. Now it seems like the WHERE clause is executed before each statement, but I want WHERE to be executed once. If the WHERE clause has a result, then none of the inserts should be executed.

To illustrate, insert some people in the table, if any records exist with one of a certain age, none of the inserts should be performed.

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 it?

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