SQL Server条件插入：匹配指定邮箱的客户仅插入一条记录

Got it, let's sort out this duplicate insertion problem. The core issue with your original query is that you're selecting from the CallRecord table—where each matching CandidateID has multiple entries—so you end up with one insert for every existing record of that candidate. We need to adjust the query to get one unique row per candidate instead.

Solution 1: Directly Query the Candidate Table (Simplest Approach) #

Since you're targeting candidates by their email addresses, you can pull the unique CandidateIDs directly from the Candidate table, which guarantees one row per candidate. This avoids any duplication from the CallRecord table entirely:

INSERT INTO CallRecord (CandidateID, Date, Notes, Action)
SELECT 
    c.id,
    '27-DECEMBER-2017',
    'Wanted to stay as active candidate following our review',
    ''
FROM Candidate c
WHERE c.email IN ('x@gmail.com', 'y@gmail.com')
GROUP BY c.id; -- Or use DISTINCT c.id instead of GROUP BY

Solution 2: Use DISTINCT on CandidateID (If You Need to Reference CallRecord) #

If you specifically need to only insert for candidates who already have entries in CallRecord, you can add DISTINCT to the CandidateID in your select clause to eliminate duplicates:

INSERT INTO CallRecord (CandidateID, Date, Notes, Action)
SELECT 
    DISTINCT cr.CandidateID,
    '27-DECEMBER-2017',
    'Wanted to stay as active candidate following our review',
    ''
FROM CallRecord cr
WHERE cr.CandidateID IN (
    SELECT id FROM Candidate WHERE email IN ('x@gmail.com', 'y@gmail.com')
);

Key Notes to Avoid Mistakes: #

• Don't forget the CandidateID! Your original query omitted inserting this field, which would leave new CallRecord entries with a blank or default CandidateID—that's almost certainly not what you want. Make sure to include it in both the INSERT and SELECT clauses.
• Both solutions ensure you only get one row per matching candidate, so you'll end up with 2 inserts instead of 5, exactly as you need.

内容的提问来源于stack exchange，提问作者Robert Woolfson