如何在SQL Server中进行多次插入之前进行一次插入

时间:2021-03-03 09:30:00

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.

我在SQL Server中使用所有insert之间的UNION进行多重插入。在查询的最后一部分,我有一个WHERE子句。现在看来,WHERE子句在每个语句之前执行,但我只希望执行一次WHERE。如果WHERE子句有结果,则不应该执行任何插入。

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?

我该怎么做呢?

1 个解决方案

#1


5  

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

#1


5  

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