merge into when not matched insert

"merge into" is a SQL statement that allows you to perform an "upsert" operation, which means to update existing records or insert new records based on certain conditions. The "when not matched insert" clause is used in conjunction with the "merge into" statement to specify what action should be taken when a record in the target table does not match a record in the source table.

For example, consider the following "merge into" statement:

MERGE INTO orders o
USING new_orders n
ON (o.order_id = n.order_id)
WHEN MATCHED THEN
  UPDATE SET o.order_status = n.order_status
WHEN NOT MATCHED THEN
  INSERT (order_id, order_status, order_date)
  VALUES (n.order_id, n.order_status, n.order_date);

In this example, we are merging data from the "new_orders" table into the "orders" table based on the "order_id" field. If a matching record is found in the "orders" table, then the "order_status" field is updated to the value in the "new_orders" table. If no matching record is found, then a new record is inserted into the "orders" table with the values from the "new_orders" table.

The "when not matched insert" clause specifies the columns to be inserted and the values to be inserted when there is no match between the target and source tables.