Copy activity - truncate and insert vs upsert

Trystan Mortimer 31 Reputation points

Assuming I do not need any data which may be removed from my source dataset, what is more efficient?

  1. Truncate a table and reinsert all data
  2. Do not truncate and use upsert



Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
8,460 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Nandan Hegde 27,241 Reputation points MVP

    With your current ask that in case if the data is no longer in source but used to come earlier and you no longer need it, the only way based on direct copy activity usage wod be to do truncate insert only. Since in case of upsert, data would either be inserted or updated but deletee record in source would always remain active in destination