如何排除sql server插入语句中的行？

问题描述：

insert into A (id, nid) 
(
    select id, 100 as nid 
    from B 
    group by id 
)

这个工作的说法，但问题是表A，对（ID，NID）主键约束，有的在计算嵌套查询行的，已经存在在表A中。我怎样才能排除它们被包含在嵌套查询中？

感谢

使用合并语句并忽略匹配 – SMor

答

你可以使用EXCEPT：

insert into A (id, nid) 
select id, 100 as nid 
from B 
group by id 
EXCEPT 
SELECT id, nid 
FROM A;

答

只是检查是否存在该行...

insert into A (id, nid) 
select id, 100 as nid 
from B 
WHERE NOT EXISTS (SELECT * FROM A WHERE A.id = B.id AND A.nid = 100) 
group by id

PS：括号围绕你的选择是不必要的

答

Add Where子句：

insert A (id, nid) 
select id, 100 as nid 
from B 
Where Not exists (Select * from A 
        Where id = B.Id 
        and nid = 100) 
group by id