How to Prevent Duplicate Records in SQL INSERT Operations Using MERGE?

Mitigating Duplicate Records in SQL INSERT Operations

Many database systems provide mechanisms to prevent duplicate records from being inserted into tables. In this case, you need to guard against duplicate insertions in a table called "Delegates" which contains the following fields:

• ID (Auto-increment, Primary)
• MemberNo
• FromYr
• ToYr

You are performing insertions using the following query:

<code class="sql">INSERT INTO Delegates ([MemNo],[FromYr],[ToYr]) values(@MemNo, @FromYr,@ToYr)</code>

However, this approach does not prevent users from mistakenly inserting duplicate records for the same member and year.

Solution Using MERGE

To avoid duplicate insertions, you can employ the MERGE statement, which provides a convenient way to perform multiple operations (insert, update, or delete) based on a comparison between two tables or a table and a set of values.

In this case, the MERGE statement can be used as follows:

<code class="sql">MERGE INTO Delegates D
USING (values(@MemNo, @FromYr,@ToYr)) X ([MemNo],[FromYr],[ToYr])
ON (insert unique key join)
WHEN NOT MATCHED BY TARGET THEN
INSERT ([MemNo],[FromYr],[ToYr]))
VALUES (X.[MemNo],X.[FromYr],X.[ToYr]);</code>

This statement accomplishes the following:

• The USING clause specifies the values to be inserted or compared against existing records.
• The ON clause defines the join condition that uniquely identifies existing records. In this case, you can use an appropriate combination of fields to ensure uniqueness (e.g., MemberNo and FromYr).
• The WHEN NOT MATCHED BY TARGET clause specifies that an insert operation should occur only if no matching record is found in the existing table.
• The VALUES clause provides the values to be inserted if a matching record is not found.

The above is the detailed content of How to Prevent Duplicate Records in SQL INSERT Operations Using MERGE?. For more information, please follow other related articles on the PHP Chinese website!