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

Asked

Viewed 1,559 times

11

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 modification date;
  • If the record does not yet exist, enter the same assignment to the current date in the field referring to the record modification date.

For example, given the table DatasBase 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 enter the date '2013-12-12' in this table, however, if it already exists in the table, I only need to update the field DateAtualizacao.

sql sql-server tsql insert

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);

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

  • 1

    Correct me if I was wrong, but I think this is the best solution because for the bank is just an operation.

  • I think that in the case of "upsert" of a line this solution should be equivalent to Luiz Ricardo’s (I did not understand the rejection of his answer). In the case of a batch upsert, my solution should be faster as it basically works like a Join, not requiring EXISTS for each line.

  • There are those think that MERGE is a little better for the reason that @Talles mentioned. Note that MERGE is available as of 2008 version of SQL Server, my solution does not have this limitation, but unfortunately the author of the question did not specify anything in this regard.

  • This method is not compatible with all SQL versions although it is right.

  • @Marcelodeaguiar I edited to make this clear.

8

In T-SQL you can use the IF and the 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)

2

My favorite way to do this is to try to update and if he can’t get there I do the Insert. I think it works on all versions of database, I’ve used from MSSQL 2005 without problems.

UPDATE DatasBase SET DataAtualizacao = @D WHERE DataBase = '2013-12-12'
IF @@ROWCOUNT = 0
  INSERT INTO DatasBase (DataBase, DataAtualizacao) VALUES ('2013-12-12', @D)

Browser other questions tagged sql sql-server tsql insert

You are not signed in. Login or sign up in order to post.