Explain SQL MERGE Upsert | Dataford Interview Questions

How does the SQL MERGE statement work (upsert into a target table)?

Asked in the Technical interview stage. Listed by the candidate as asked in their HCL Data Engineer (4+ YOE) interview. Write SQL to apply the staged catalog rows to the target catalog.

Output

Return one row per final catalog item with item_code, item_name, category, unit_price, and active.
Include updated existing items and newly inserted items, ordered by item_code ascending.

Schema

product_catalog

Column	Type	Description
item_codePK	VARCHAR(12)	Unique catalog item code
item_name	VARCHAR(100)	Catalog item name
category	VARCHAR(40)	Product category
unit_price	NUMERIC(10,2)	Current price per unit
active	BOOLEAN	Whether the item is currently active

product_catalog_stage

Column	Type	Description
item_codePK	VARCHAR(12)	Unique staged catalog item code
item_name	VARCHAR(100)	Staged item name
category	VARCHAR(40)	Staged product category
unit_price	NUMERIC(10,2)	Staged price per unit
active	BOOLEAN	Staged active status

Your question is Explain SQL MERGE Upsert. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem