2
Across of that question felt the need to research and understand a little about the UPSERT. I was wondering What blessed command is that? I’ve never seen..
I found some information but it is not clear to me because there is no "basic command", like UPSERT tabela (id=0, valor='nome')
, for example.
I usually see codes using the following structure:
IF EXISTS (SELECT 1 FROM tabela WHERE id = 0)
UPDATE tabela SET campo = 2 where id = 0
ELSE
INSERT INTO tabela (id, campo) VALUES (0, 1)
END
About upsert found that way:
MERGE INTO tabela
USING (SELECT 0 AS id, 1 AS campo) AS reg
ON tabela.id = reg.id
WHEN MATCHED THEN
UPDATE SET campo = reg.campo
WHEN NOT MATCHED THEN
INSERT(id , campo)
VALUES (reg.id, reg.campo)
Therefore, with regard to performance, what form should I use? o IF
which consults the existence by the identifier and defines which flow to follow (Insert or update) or the MERGE
(upsert) condensing the two commands?
my main point would be "exist a command (keyword) with the name upsert". I understand that the
merge
works like this commando (and that yes, it is a single block).– rLinhares
I found valid your answer; really the first block (
if
) has separate operations and perhaps duplicate validation, but still may have better performance than the second (follows the doubt =p)– rLinhares