How to condition the insertion of a record in SQL Server?

Question:

How can I condition the insertion of a record in a table in SQL Server using the T-SQL language considering the following rules:

  • If the record to be inserted already exists in the table, only update the field referring to the record's modification date;
  • If the record does not yet exist, insert it attributing the current date in the field referring to the record's modification date.

For example, given the DatasBase table with the following structure:

+--------------------------------------+
|              DatesBase               |
+--------------------------------------+
| PK DateBase        DATETIME NOT NULL |
|    DateAtualizacao DATETIME NOT NULL |
+--------------------------------------+

where, I have a record of used dates in another table with their respective update dates. We assume that we need to insert the date '2013-12-12' in this table, however, if it already exists in the table, I just need to update the DateAtualizacao field.

Answer:

You can use a MERGE (available starting with SQL Server 2008):

MERGE DatasBase AS existentes
USING (SELECT '2013-12-13', '2013-12-14') AS novos (DateBase, DataAtualizacao)
  ON (existentes.DateBase = novos.DateBase)
WHEN MATCHED THEN
  UPDATE SET DataAtualizacao = novos.DataAtualizacao
WHEN NOT MATCHED THEN
  INSERT (DateBase, DataAtualizacao)
  VALUES (novos.DateBase, novos.DataAtualizacao);

http://sqlfiddle.com/#!6/797ae/16

Post Views: 0
Scroll to Top