How to Prevent Duplicate Values during INSERT in SQL?

Preventing Duplicate Values during INSERT in SQL

Given a table "Delegates" with columns "ID," "MemberNo," "FromYr," and "ToYr," the user needs to prevent the insertion of duplicate values from user input. The original query, INSERT INTO Delegates ([MemNo],[FromYr],[ToYr]) values(@MemNo, @FromYr,@ToYr), allows for duplicate entries for the same member and year.

To address this issue, the user can employ the MERGE command. MERGE combines the functionality of INSERT, UPDATE, and DELETE statements into a single operation. The following MERGE statement will achieve the desired behavior:

<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 MERGE statement does the following:

• It first attempts to join the rows in the "Delegates" table (D) with the values provided by the user (X).
• If a match is found (based on the specified join condition, which should be a unique key), it updates the existing row.
• If no match is found, it inserts a new row with the values from X.

By using this MERGE statement, the user can ensure that duplicate values are not inserted into the "Delegates" table.

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