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

Navigation
11

How can I condition the insertion of a record into 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 modification date;
  • If the record still does not exist, insert the same assignment as the current date in the field for the record 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 dates used in another table with their respective update dates. We assume that we need to insert date '2013-12-12' into this table, however, if it already exists in the table, I just need to update the DateAtualizacao field.

    
asked by anonymous 12.12.2013 / 17:15

3 answers

10

You can use a MERGE (available from 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);

link

    
12.12.2013 / 18:02
8

In T-SQL you can use IF and EXISTS . Example: 

DECLARE @D DATETIME
SET @D = GETDATE()
IF EXISTS(SELECT 1 FROM DatasBase WHERE DataBase = '2013-12-12')
    UPDATE DatasBase SET DataAtualizacao = @D WHERE DataBase = '2013-12-12'
ELSE 
    INSERT INTO DatasBase (DataBase, DataAtualizacao) VALUES ('2013-12-12', @D)
    
12.12.2013 / 17:34
2

My preferred way to do this is to try to update and if it does not, I'll do the insert. I think it works on all versions of the database, I've used it from MSSQL 2005 without any problems. 

UPDATE DatasBase SET DataAtualizacao = @D WHERE DataBase = '2013-12-12'
IF @@ROWCOUNT = 0
  INSERT INTO DatasBase (DataBase, DataAtualizacao) VALUES ('2013-12-12', @D)
    
13.12.2013 / 12:49
Image rotation in java How to make a multilingual website?