在SQL Server中如何防止INSERT INTO SELECT查询中的重复

I want to prevent the insert duplicates values in table to solve this issue i use the syntax "WHERE NOT EXISTS" but not working so please what is the correct syntax to solve this problem.

我想阻止表中的插入重复值来解决此问题我使用语法“WHERE NOT EXISTS”但不工作所以请问什么是解决此问题的正确语法。

INSERT INTO [JPCustomer] ([CustomerID ],
                           [JPID],
                           [Frequency],
                           [StartWeek],
                           [sat],
                           [sun],
                           [mon],
                           [tue],
                           [wed],
                           [thu],
                           [fri],
                           [VisitOrder],
                           [ModifiedOn],
                           [ModifiedBy],
                           [CreatedOn],
                           [Createdby],
                           [RecordSource],
                           [IsPotential])
select cu.CustomerNo,
       jp.ID,
        4,
       1,
        1,
        1,
        1,
        1,
        1,
        1,
        1,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        0,
        0 
from CUSTOMERNO# cu
join SalesmanNo# sa on cu.OCCURRENCE = sa.OCCURRENCE
join JourneyPlan JP on jp.AssignedTO = sa.SalesmanNo
WHERE NOT EXISTS (select j.CustomerID,j.JPID from JPCustomer j)

1 个解决方案

#1

Your where not exists does not compare properly:

你不存在的地方不能正确比较：

WHERE NOT EXISTS 
(
select 1 -- does not matter what you return, exists will be true if any value comes back
from JPCustomer j 
where j.CustomerID = cu.customerid  -- match on customerid field
and j.JPID = jp.id  -- match on id field
)