In many cases, especially in ETL processes or data loading to a data warehouse, for example, we would rather use a single sentence or the checking of the existence of a register and its updating. If register doesn't exist we would want to insert it. This combination has been nicknamed UPSERT, although in SQL there is a specific statement to make it, which is MERGE.
In this post are presented some simple solutions to make INSERTS or UPDATES with SQL Server, depending on the presence or absence of incoming registers. Changing the syntax a little we can do the same in other database engines such as Oracle or MySQL.
UPSERT in SQL Server
The SQL sentence that allows the mixing of data from two tables in one is MERGE. It has the peculiarity that its syntax allows specifying an SQL statement when the record being inserted already exists (if the fields defined are equal), and other different statement when there is no register in the target table with matching values in the specified fields. The normal thing is to define an update if the register already exists or an insert in the opposite case.
Merge to update/insert a Register
It becomes clearer with this example of how to insert or update fixed values for a particular register, which could be useful in a DWH environment for example, to insert in one dimension the fictional register corresponding to id's not found (or dummy):
MERGE Target_Table WITH(HOLDLOCK) as target using (values ('value1', 'value2')) as source (field1, field2) on target.idRegister = 23 when matched then update set field1_dest = source.field1, field2_dest = source.field2 when not matched then insert ( idRegister, field1_dest, field2_dest) values ( 23, source.field1, source.codenterprise);If registration with idRegister=23 exists, fields field2_dest and field1_dest are updated with values 'value1' and 'value2', respectively.
If doesn't exist, you insert a register with idRegister=23, value1_dest='value1' and value2_dest='value2'
Transactions to update / insert a record
Another simple solution to insert / update a record, but using transactions rather than the MERGE statement would be:
(With a cursor and parameters it can be easily implemented to update / insert more Registers)
BEGIN TRAN SELECT idRegister FROM Target_Table WHERE idRegister=23 IF @@ROWCOUNT = 0 insert ( idRegister, field1_dest, field2_dest) values ( 23, ‘value1’, ‘value2’); COMMIT TRAN
It makes an update of the table. If now is not updated, the register doesn't exist, and then an insert is done.
Merge to update / insert multiple Registers
The previous merge could only insert a single register, but MERGE take full tables as a data source, or simply registers as a result of any select statement (SELECT), which enables us to create more powerful sentences, to run more efficient operations working together, and normally being faster than transactions similar to the previous one.
For example, to insert or update values from a table or view:
MERGE Target_Table WITH(HOLDLOCK) as target using (select idRegister, field1, field2 from Tabla_Origen) as source (field1, field2) on (target.idRegister = source.idRegister) when matched then update set field1_dest = source.field1, field2_dest = source.field2 when not matched then insert ( idRegister, field1, field2) values ( source.idRegister, source.field1, source.field2);
References
I link some references to conclude and extend the information:
- Information around MERGE and UPSERT, from Wikipedia
MERGE (SQL) - Solutions for INSERT or UPDATE, from Stackoverflow
Solutions for INSERT or UPDATE on SQL Server - Comparing performance for the MERGE statement in SQL Server, from MSSQLTips
Comparing performance for the MERGE statement to SELECT, INSERT, UPDATE or DELETE - MERGE statements sintax, from Microsoft MSDN libraries
MERGE (Transact-SQL) - How to use MERGE in Oracle avoiding a wide-extended error:
UPDATE con JOIN en Oracle
By Carlos Fernández
