A nightly batch adds new inter-branch loan records to a Warehouse table, but some records represent updates to loans that were already loaded the previous night — for example, a loan's return date being filled in late. A straight INSERT would create duplicate rows for those loans. Which T-SQL approach correctly applies both new and updated records in a single operation?

A MERGE statement compares an incoming batch against the target table on a matching key — here, the loan's unique identifier — and applies an UPDATE when a match is found while performing an INSERT when it isn't, handling both new and late-updated records in a single, atomic operation without creating duplicates. That upsert behavior is exactly what a mix of new and revised loan records needs. Truncating the table and reinserting the full batch would work only if the nightly batch were a complete, authoritative snapshot of every loan ever recorded, which isn't stated here and would also throw away any history not present in that night's extract. Creating an index on the loan key enforces uniqueness constraints or speeds up lookups, but it doesn't decide whether to insert or update a row — a duplicate-key insert would simply fail or violate the constraint rather than being intelligently merged. SELECT DISTINCT removes duplicate rows within the incoming batch itself, but it does nothing to reconcile that batch against rows that already exist in the target table from a previous night. Before running the MERGE nightly, confirm the loan key is genuinely unique across branches, since a key collision between two different branches' loans would silently overwrite the wrong record.