SQL Server使用ON DUPLICATE KEY UPDATE语法报错，无该语句则重复插入

Ah, I spot the problem right away! The ON DUPLICATE KEY UPDATE syntax you're using is a MySQL-specific feature—but you're working with Microsoft SQL Server, which doesn't recognize that keyword. That's exactly why you're hitting the Incorrect syntax near the keyword 'ON' error.

SQL Server uses the MERGE statement for upsert operations (inserting new rows and updating existing ones when duplicates are detected). Here's how to adjust your code to work properly:

Step 1: Verify Your Table Has a Unique Constraint #

First, make sure TABLE1 has a unique constraint (either a primary key or a unique index) that defines what counts as a "duplicate". For example, if rows are duplicates when building and course are the same together, you should have a composite unique constraint on those two columns. Without this, SQL Server won't know how to identify which rows to update vs. insert.

Step 2: Use the MERGE Statement for Bulk Upsert #

Replace your INSERT query with a MERGE query tailored to SQL Server:

# Define the MERGE query - adjust the ON clause to match your unique constraint
sql_merge_query = """
MERGE INTO TABLE1 AS Target
USING (VALUES (?, ?, ?, ?, ?, ?)) AS Source (building, course, description, course_type, course_type_desc, dual_credit)
ON Target.building = Source.building AND Target.course = Source.course  # Match on your unique key(s)
WHEN MATCHED THEN
    UPDATE SET
        description = Source.description,
        course_type = Source.course_type,
        course_type_desc = Source.course_type_desc,
        dual_credit = Source.dual_credit
WHEN NOT MATCHED THEN
    INSERT (building, course, description, course_type, course_type_desc, dual_credit)
    VALUES (Source.building, Source.course, Source.description, Source.course_type, Source.course_type_desc, Source.dual_credit);
"""

# Execute the bulk upsert
cursor.executemany(sql_merge_query, listCourse)
conn.commit()  # Don't forget to commit the transaction!

Key Notes: #

• Adjust the ON Clause: Modify Target.building = Source.building AND Target.course = Source.course to match your actual unique constraint. If your unique key is a single column (e.g., course), simplify it to Target.course = Source.course.
• Parameter Order: Ensure the order of values in each entry of listCourse matches the order of placeholders (?, ?, ?, ?, ?, ?) and the column definitions in the Source clause.
• Commit the Transaction: Always call conn.commit() after executing the query to save your changes to the database.

Why This Works #

The MERGE statement lets you:

1. Compare incoming rows (from your listCourse) against existing rows in TABLE1 using the ON condition.
2. If a match is found (duplicate row), update the existing row with the new values.
3. If no match is found, insert the new row into the table.

This achieves exactly what you wanted: bulk insertion without duplicates, no syntax errors, and compatibility with SQL Server.

内容的提问来源于stack exchange，提问作者Mike Lim