in SQL Server How to prevent duplicates in INSERT INTO SELECT query

Question

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.

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)

Do you already have duplicates in your select statement?

Esteban P.
– Esteban P.

2017-09-04 14:44:16 +00:00
Commented Sep 4, 2017 at 14:44 — Esteban P.
– Esteban P., Commented Sep 4, 2017 at 14:44
You can check by trigger...

Dr. X
– Dr. X

2017-09-04 14:47:21 +00:00
Commented Sep 4, 2017 at 14:47 — Dr. X
– Dr. X, Commented Sep 4, 2017 at 14:47

JohnHC · Accepted Answer · 2017-09-04 14:49:29Z

3

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
)

answered Sep 4, 2017 at 14:49

JohnHC

11.2k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

in SQL Server How to prevent duplicates in INSERT INTO SELECT query

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related