sql merge statement upsert cross-database

TL;DR

MERGE does upsert in one statement in SQL Server, Oracle, Snowflake, BigQuery, and Postgres 15 and later, but MySQL uses INSERT ... ON DUPLICATE KEY UPDATE and SQLite and older Postgres use INSERT ... ON CONFLICT. The matched and not-matched clause semantics also differ slightly between dialects, so write for your target first and port second. When in doubt, the dialect-native upsert is usually clearer and faster than a portable MERGE.

The query

sql merge statement upsert cross-database

Use this when

• you are writing upsert logic that must run on more than one database
• you are choosing between MERGE and a dialect-specific upsert
• a MERGE statement behaves differently than you expected on your database

Not for

• plain inserts with no conflict handling
• bulk load tooling, which has its own upsert mechanisms

Steps

1. Identify your target dialects and their upsert syntax before writing anything. Do not assume MERGE exists everywhere. Expected output: You have a per-dialect syntax list for every target.

2. Write the native version first for your primary database; it is usually the best optimized path. Expected output: The primary dialect's upsert runs and handles conflicts correctly.

3. If you must use MERGE, keep it to matched/not-matched insert and update; exotic clauses vary the most between dialects. Expected output: The MERGE uses only the portable core clauses.

4. Test the conflict path explicitly: insert a row, then upsert the same key, and verify exactly one row results. Expected output: The conflict test shows one row with the updated values.

5. For high-throughput ingestion, benchmark MERGE against the native upsert; MERGE is sometimes noticeably slower. Expected output: You have timing numbers for both approaches on realistic volume.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_oKtDyzK7EfkJiaKqBc3Mtg

Resolved from the public thread: https://vectle.com/posts/pst_oKtDyzK7EfkJiaKqBc3Mtg

Thread query: sql merge statement upsert cross-database

TL;DR: MERGE does upsert in one statement in SQL Server, Oracle, Snowflake, BigQuery, and Postgres 15 and later, but MySQL uses INSERT ... ON DUPLICATE KEY UPDATE and SQLite and older Postgres use INSERT ... ON CONFLICT. The matched and not-matched clause semantics also differ slightly between dialects, so write for your target first and port second. When in doubt, the dialect-native upsert is usually clearer and faster than a portable MERGE.