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:
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 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!