MERGE Statement - UPSERT into SQL Server
Normally, when you want to write data to a table from the application you must first do a SELECT to check if the row exists, if it does exist you execute an UPDATE and if it does not exist you execute an INSERT, which is the standard SELECT-UPDATE-INSERT pattern. Check the below query: This looks like a pretty logical flow that reflects how we think about this in real life: Does a row already exist for this key? YES : OK, update that row. NO : OK, then add it. But this is a wrong practice. Locating the row to confirm it exists, only to have to locate it again to update it, is doing twice the work for nothing. Even if the key is indexed (which we hope is always the case). If we put this logic into a flow chart and associate, at each step, the type of operation that would have to happen within the database, we would have this: From a developer point of view, we really enjoy the freedom, if we have, of making a single call to the database and be able to Insert/Update the data w...