Comment effectuer efficacement des upserts dans SQL Server ?

Effectuez efficacement des opérations d'insertion ou de mise à jour dans SQL Server

Lorsque vous traitez des opérations CRUD, vous rencontrez souvent des situations dans lesquelles vous devez mettre à jour des enregistrements existants ou insérer de nouveaux enregistrements lorsque la clé primaire n'existe pas. Ceci équivaut à la logique "IF (la clé primaire existe) UPDATE else INSERT".

La question est : comment y parvenir avec des performances optimales ?

Solution SQL Server

SQL Server propose plusieurs solutions :

1. Utilisez l'instruction MERGE

L'instruction

MERGE fournit un moyen concis d'implémenter cette logique. Il permet de spécifier une opération de mise à jour à effectuer si la clé primaire existe, et une opération d'insertion à effectuer si la clé primaire n'existe pas.

MERGE INTO MyTable WITH (HOLDLOCK) AS Target
USING (VALUES (@key, @datafield1, @datafield2)) AS Source(KEY, datafield1, datafield2)
ON (Target.KEY = Source.KEY)
WHEN MATCHED THEN
    UPDATE SET datafield1 = Source.datafield1, datafield2 = Source.datafield2
WHEN NOT MATCHED THEN
    INSERT (KEY, datafield1, datafield2)
    VALUES (Source.KEY, Source.datafield1, Source.datafield2);

2. Utiliser les transactions

Une autre approche consiste à utiliser des transactions pour garantir l'intégrité des données et éviter les conflits de clés primaires.

BEGIN TRANSACTION;
IF EXISTS (SELECT * FROM MyTable WITH (XLOCK) WHERE KEY = @key)
BEGIN
    UPDATE MyTable SET datafield1 = @datafield1, datafield2 = @datafield2
    WHERE KEY = @key;
END
ELSE
BEGIN
    INSERT INTO MyTable (KEY, datafield1, datafield2)
    VALUES (@key, @datafield1, @datafield2);
END
COMMIT TRANSACTION;

3. Utilisez la clause OUTPUT en conjonction avec UPDATE et INSERT

Une troisième option consiste à utiliser une clause OUTPUT pour capturer le nombre de lignes affectées par l'opération UPDATE. Ces informations peuvent ensuite être utilisées pour déterminer si une opération INSERT est requise.

BEGIN TRANSACTION;
UPDATE MyTable SET datafield1 = @datafield1, datafield2 = @datafield2
OUTPUT inserted.KEY INTO @AffectedRows
WHERE KEY = @key;

IF @AffectedRows = 0
BEGIN
    INSERT INTO MyTable (KEY, datafield1, datafield2)
    VALUES (@key, @datafield1, @datafield2);
END
COMMIT TRANSACTION;

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!