Question Solutions pour INSERT OR UPDATE sur SQL Server


Supposons une structure de table de MyTable(KEY, datafield1, datafield2...).

Souvent, je veux soit mettre à jour un enregistrement existant, soit insérer un nouvel enregistrement s'il n'existe pas.

Essentiellement:

IF (key exists)
  run update command
ELSE
  run insert command

Quelle est la meilleure façon d'écrire ceci?


493
2017-09-20 15:00


origine


Réponses:


ne pas oublier les transactions. La performance est bonne, mais l'approche simple (SI EXISTE ..) est très dangereuse.
Lorsque plusieurs threads vont essayer d'effectuer une insertion ou une mise à jour, vous pouvez facilement obtenir la violation de clé primaire.

Les solutions fournies par @Beau Crawford & @Esteban montrent une idée générale mais sont sujettes à des erreurs.

Pour éviter les blocages et les violations PK, vous pouvez utiliser quelque chose comme ceci:

begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert into table (key, ...)
   values (@key, ...)
end
commit tran

ou

begin tran
   update table with (serializable) set ...
   where key = @key

   if @@rowcount = 0
   begin
      insert into table (key, ...) values (@key,..)
   end
commit tran

307
2017-09-20 15:06



Voir mon réponse détaillée à une question précédente très similaire

@Beau Crawford est un bon moyen dans SQL 2005 et ci-dessous, mais si vous accordez rep, il devrait aller à la premier gars à SO it. Le seul problème est que pour les insertions, il y a encore deux opérations d'E / S.

MS Sql2008 introduit merge à partir du standard SQL: 2003:

merge tablename with(HOLDLOCK) as target
using (values ('new value', 'different value'))
    as source (field1, field2)
    on target.idfield = 7
when matched then
    update
    set field1 = source.field1,
        field2 = source.field2,
        ...
when not matched then
    insert ( idfield, field1, field2, ... )
    values ( 7,  source.field1, source.field2, ... )

Maintenant, ce n'est vraiment qu'une opération IO, mais un code terrible :-(


350
2017-10-28 15:09



Faites un UPSERT:

UPDATE MyTable SET FieldA = @ FieldA WHERE Clé = @ Clé

SI @@ ROWCOUNT = 0
   INSERT INTO MyTable (FieldA) VALUES (@FieldA)

http://en.wikipedia.org/wiki/Upsert


144
2017-09-20 15:04



Beaucoup de gens vous suggéreront d'utiliser MERGE, mais je vous préviens contre. Par défaut, il ne vous protège pas des conditions de concurrence et de course plus que des instructions multiples, mais il introduit d'autres dangers:

http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/

Même avec cette syntaxe "plus simple" disponible, je préfère encore cette approche (la gestion des erreurs est omise par souci de brièveté):

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
UPDATE dbo.table SET ... WHERE PK = @PK;
IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.table(PK, ...) SELECT @PK, ...;
END
COMMIT TRANSACTION;

Beaucoup de gens vont suggérer cette façon:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
IF EXISTS (SELECT 1 FROM dbo.table WHERE PK = @PK)
BEGIN
  UPDATE ...
END
ELSE
  INSERT ...
END
COMMIT TRANSACTION;

Mais tout ce que cela permet de faire, c'est de vous assurer que vous devrez peut-être lire le tableau deux fois pour localiser les lignes à mettre à jour. Dans le premier exemple, il vous suffira de localiser la ou les lignes une seule fois. (Dans les deux cas, si aucune ligne n'est trouvée à partir de la lecture initiale, une insertion se produit.)

D'autres suggéreront ceci:

BEGIN TRY
  INSERT ...
END TRY
BEGIN CATCH
  IF ERROR_NUMBER() = 2627
    UPDATE ...
END CATCH

Cependant, ceci est problématique si, pour la seule raison, laisser SQL Server attraper des exceptions que vous auriez pu empêcher en premier lieu est beaucoup plus cher, sauf dans le cas rare où presque chaque insertion échoue. Je prouve autant ici:


73
2018-01-18 20:12



IF EXISTS (SELECT * FROM [Table] WHERE ID = rowID)
UPDATE [Table] SET propertyOne = propOne, property2 . . .
ELSE
INSERT INTO [Table] (propOne, propTwo . . .)

Modifier: 

Hélas, même à mon propre détriment, je dois admettre que les solutions qui font cela sans sélection semblent être meilleures puisqu'elles accomplissent la tâche avec une étape de moins.


40
2017-09-20 15:02



Si vous voulez UPSERT plusieurs enregistrements à la fois, vous pouvez utiliser l'instruction DML ANSI SQL: 2003 MERGE.

MERGE INTO table_name WITH (HOLDLOCK) USING table_name ON (condition)
WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])

Check-out Imitation d'une instruction MERGE dans SQL Server 2005.


34
2017-09-20 15:49



Bien que ce soit assez tard pour commenter cela, je veux ajouter un exemple plus complet en utilisant MERGE.

Ces instructions Insert + Update sont généralement appelées des instructions "Upsert" et peuvent être implémentées à l'aide de MERGE dans SQL Server.

Un très bon exemple est donné ici: http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx

Ce qui précède explique également les scénarios de verrouillage et de concurrence.

Je citerai le même pour référence:

ALTER PROCEDURE dbo.Merge_Foo2
      @ID int
AS

SET NOCOUNT, XACT_ABORT ON;

MERGE dbo.Foo2 WITH (HOLDLOCK) AS f
USING (SELECT @ID AS ID) AS new_foo
      ON f.ID = new_foo.ID
WHEN MATCHED THEN
    UPDATE
            SET f.UpdateSpid = @@SPID,
            UpdateTime = SYSDATETIME()
WHEN NOT MATCHED THEN
    INSERT
      (
            ID,
            InsertSpid,
            InsertTime
      )
    VALUES
      (
            new_foo.ID,
            @@SPID,
            SYSDATETIME()
      );

RETURN @@ERROR;

10
2018-01-21 05:47



/*
CREATE TABLE ApplicationsDesSocietes (
   id                   INT IDENTITY(0,1)    NOT NULL,
   applicationId        INT                  NOT NULL,
   societeId            INT                  NOT NULL,
   suppression          BIT                  NULL,
   CONSTRAINT PK_APPLICATIONSDESSOCIETES PRIMARY KEY (id)
)
GO
--*/

DECLARE @applicationId INT = 81, @societeId INT = 43, @suppression BIT = 0

MERGE dbo.ApplicationsDesSocietes WITH (HOLDLOCK) AS target
--set the SOURCE table one row
USING (VALUES (@applicationId, @societeId, @suppression))
    AS source (applicationId, societeId, suppression)
    --here goes the ON join condition
    ON target.applicationId = source.applicationId and target.societeId = source.societeId
WHEN MATCHED THEN
    UPDATE
    --place your list of SET here
    SET target.suppression = source.suppression
WHEN NOT MATCHED THEN
    --insert a new line with the SOURCE table one row
    INSERT (applicationId, societeId, suppression)
    VALUES (source.applicationId, source.societeId, source.suppression);
GO

Remplacez les noms de table et de champ par tout ce dont vous avez besoin. Prend soin de utilisant ON condition. Définissez ensuite la valeur appropriée (et le type) pour les variables sur la ligne DECLARE.

À votre santé.


6
2017-10-28 15:28



Vous pouvez utiliser MERGE Statement, Cette instruction est utilisée pour insérer des données si elle n'existe pas ou si elle existe.

MERGE INTO Employee AS e
using EmployeeUpdate AS eu
ON e.EmployeeID = eu.EmployeeID`

5
2017-10-12 18:03