Solutions pour insérer ou mettre à jour sur le serveur SQL

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

@Beau Crawford est un bon moyen dans SQL 2005 et ci-dessous, bien que si vous accordez rep il devrait aller au premier gars à SO it . Le seul problème, c'est que pour les inserts, c'est toujours deux opérations IO.

MS Sql2008 introduit merge de la norme 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 c'est vraiment juste une opération D'IO, mais code terrible: - (

Do an UPSERT:

UPDATE MyTable SET FieldA=@FieldA WHERE Key=@Key

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

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

beaucoup de gens vous suggéreront d'utiliser MERGE , mais je vous mets en garde contre cela. Par défaut, il ne vous protège pas de la concurrence et des conditions de course plus que de multiples déclarations, 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 (gestion des erreurs omis par souci de concision):

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 suggéreront 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 cela permet de s'assurer que vous pouvez avoir besoin de lire le tableau deux fois pour localiser la(Les) ligne (s) à mettre à jour. Dans le premier exemple, vous n'aurez besoin de localiser la ou les lignes qu'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 cette voie:

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

cependant, ceci est problématique si pour aucune autre raison que de laisser les exceptions SQL Server catch que vous auriez pu prévenir en premier lieu est beaucoup plus cher, sauf dans le cas rare où presque chaque insert échoue. Je le prouve ici:

• http://www.mssqltips.com/sqlservertip/2632/checking-for-potential-constraint-violations-before-entering-sql-server-try-and-catch-logic/
• http://www.sqlperformance.com/2012/08/t-sql-queries/error-handling

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

Edit:

hélas, même à mon détriment, je dois admettre que les solutions qui font cela sans un select semblent être mieux puisqu'elles accomplissent la tâche avec un pas de moins.

si vous voulez UPSERT plus d'un enregistrement à la fois, vous pouvez utiliser la déclaration ANSI SQL:2003 DML 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 Mimicking MERGE Statement in SQL Server 2005 .

bien qu'il soit assez tard pour commenter ceci, je voudrais ajouter un exemple plus complet en utilisant MERGE.

tels Insert + UPDATE statements sont généralement appelés "upsert" statements et peuvent être implémentés en utilisant MERGE in 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 le verrouillage et la simultanéité des scénarios.

je citerai la même chose 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;

/*
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

remplacer les noms de table et de champ par tout ce dont vous avez besoin. Prenez soin de la en utilisant sur condition. Ensuite, définissez la valeur (et le type) appropriée pour les variables sur la ligne de déclaration.

santé.

vous pouvez utiliser la déclaration MERGE , cette déclaration est utilisée pour insérer des données si elles n'existent pas ou mettre à jour si elles existent.

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

si vous allez à la mise à jour si-no-rows-updated puis INSERT route, envisager de faire L'INSERT d'abord pour prévenir une condition de course (en supposant qu'aucune suppression intervienne)

INSERT INTO MyTable (Key, FieldA)
   SELECT @Key, @FieldA
   WHERE NOT EXISTS
   (
       SELECT *
       FROM  MyTable
       WHERE Key = @Key
   )
IF @@ROWCOUNT = 0
BEGIN
   UPDATE MyTable
   SET FieldA=@FieldA
   WHERE Key=@Key
   IF @@ROWCOUNT = 0
   ... record was deleted, consider looping to re-run the INSERT, or RAISERROR ...
END

outre le fait d'éviter une condition de course, si dans la plupart des cas le record existe déjà, cela provoquera l'échec de L'insertion, gaspillant CPU.

en utilisant MERGE probablement préférable pour SQL2008 à partir.

dans SQL Server 2008, vous pouvez utiliser la déclaration de fusion

cela dépend du modèle d'utilisation. Il faut regarder la vue d'ensemble de l'utilisation sans se perdre dans les détails. Par exemple, si le modèle d'utilisation est mis à jour à 99% après que l'enregistrement a été créé, alors le "UPSERT" est la meilleure solution.

après le premier insert (hit), ce sera toutes les mises à jour de déclaration simple, pas de SI ou de mais. La condition " où " sur l'insert est nécessaire sinon il va insérer des doubles, et vous ne voulez pas traiter avec le verrouillage.

UPDATE <tableName> SET <field>=@field WHERE key=@key;

IF @@ROWCOUNT = 0
BEGIN
   INSERT INTO <tableName> (field)
   SELECT @field
   WHERE NOT EXISTS (select * from tableName where key = @key);
END

MS SQL Server 2008 introduit la déclaration de fusion, qui je crois fait partie de la norme SQL:2003. Comme beaucoup l'ont montré, il n'est pas très difficile de gérer une rangée de cas, mais lorsque l'on traite de grands ensembles de données, on a besoin d'un curseur, avec tous les problèmes de performance qui se présentent. La déclaration de fusion sera un ajout très apprécié lors du traitement de grands ensembles de données.

avant que tout le monde saute à HOLDLOCK-s par peur de ces utilisateurs nafarious exécutant vos sproc directement :-) permettez-moi de souligner que vous devez garantir l'unicité de la nouvelle PK-s par la conception (clés d'identité, générateurs de séquence dans Oracle, index uniques pour les ID-S externes, requêtes couvertes par index). C'est l'alpha et l'oméga de la question. Si vous ne l'avez pas, aucun Hold-LOCK de l'univers ne va vous sauver et si vous l'avez, alors vous n'avez pas besoin tout ce qui est au-delà de UPDLOCK sur le premier select (ou pour utiliser update first).

fonctionnent normalement dans des conditions très contrôlées et avec l'hypothèse d'un interlocuteur de confiance (niveau moyen). Ce qui signifie que si un simple motif upsert (update+insert ou merge) voit dupliquer PK cela signifie un bug dans votre mid-tier ou la conception de table et il est bon que SQL va crier un défaut dans un tel cas et rejeter l'enregistrement. Placer une retenue dans ce cas équivaut à manger des exceptions et à accepter des données potentiellement erronées, en plus de réduire votre perf.

cela dit, utiliser MERGE, ou UPDATE then INSERT est plus facile sur votre serveur et moins sujet aux erreurs puisque vous n'avez pas à vous rappeler d'ajouter (UPDLOCK) pour sélectionner en premier. Aussi, si vous faites des insertions/mises à jour par petits lots, vous devez connaître vos données afin de déterminer si une opération est approprié ou non. Il s'agit simplement d'une collection de documents sans rapport, puis une transaction "enveloppante" supplémentaire sera préjudiciable.

est-ce que les conditions de course ont vraiment de l'importance si vous essayez d'abord une mise à jour suivie d'un insert? Disons que vous avez deux threads souhaitez définir une valeur pour la clé clé :

Thread 1: valeur = 1

Filetage 2: valeur = 2

exemple de scénario de condition de race

1. clé n'est pas défini
2. Thread 1 échoue avec la mise à jour
3. Thread 2 échoue avec la mise à jour
4. exactement un de thread 1 ou thread 2 succède à insert. Par exemple: fil 1

5. l'autre thread ne fonctionne pas avec insert (avec la clé error duplicate) - thread 2.

   • résultat: la" première " des deux marches à insérer, détermine la valeur.
   • résultat souhaité: le dernier des 2 threads pour écrire des données (mise à jour ou insertion) doit décider de la valeur

mais, dans un environnement multithread, L'ordonnanceur D'OS décide de l'ordre d'exécution du thread - dans le scénario ci-dessus, où nous avons cette condition de course, c'est L'OS qui a décidé de la séquence d'exécution. Ie: il est faux de dire que "thread 1" ou "thread 2" était "premier" du point de vue du système.

quand le temps d'exécution est si proche pour le thread 1 et le thread 2, le résultat de la course la condition n'a pas d'importance. La seule exigence devrait être que l'un des threads définisse la valeur résultante.

pour la mise en œuvre: si la mise à jour suivie de l'insertion entraîne une erreur "duplicate key", celle-ci doit être considérée comme un succès.

aussi, on ne devrait bien sûr jamais supposer que la valeur dans la base de données est la même que la valeur que vous avez écrite en dernier.

j'avais essayé la solution ci-dessous et cela fonctionne pour moi, quand la requête concurrente pour insérer la déclaration se produit.

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

Vous pouvez utiliser cette requête. Travailler dans toutes les éditions de SQL Server. C'est simple et clair. Mais vous devez utiliser 2 requêtes. Vous pouvez utiliser si vous ne pouvez pas utiliser MERGE

    BEGIN TRAN

    UPDATE table
    SET Id = @ID, Description = @Description
    WHERE Id = @Id

    INSERT INTO table(Id, Description)
    SELECT @Id, @Description
    WHERE NOT EXISTS (SELECT NULL FROM table WHERE Id = @Id)

    COMMIT TRAN

NOTE: Veuillez expliquer les réponses négatives

si vous utilisez ADO.NET, le DataAdapter s'occupe de ça.

si vous voulez vous en occuper vous-même, c'est comme ça:

assurez-vous qu'il y a une contrainte de clé primaire sur votre colonne de clé.

puis vous:

1. faire la mise à jour
2. si la mise à jour échoue parce qu'un enregistrement avec la clé existe déjà, faire l'insertion. Si la mise à jour n'échoue pas, vous êtes finis.

, Vous pouvez aussi faire l'inverse, c'est à dire ne l'insérez d'abord, et de faire la mise à jour si l'insertion échoue. Normalement, la première Solution est meilleure, parce que les mises à jour sont faites plus souvent que les inserts.

Faire un si existe ... autre. .. implique de faire au moins deux demandes (une pour vérifier, une pour prendre des mesures). L'approche suivante n'exige qu'un seul enregistrement lorsque celui-ci existe, deux si un insert est requis:

DECLARE @RowExists bit
SET @RowExists = 0
UPDATE MyTable SET DataField1 = 'xxx', @RowExists = 1 WHERE Key = 123
IF @RowExists = 0
  INSERT INTO MyTable (Key, DataField1) VALUES (123, 'xxx')

je fais habituellement ce que plusieurs autres posters ont dit en ce qui concerne la vérification de lui existant d'abord et puis faire ce que le chemin correct est. Une chose que vous devriez vous rappeler en faisant ceci est que le plan d'exécution caché par sql pourrait être non optimal pour un chemin ou l'autre. Je crois que la meilleure façon de le faire est d'appeler deux différentes procédures stockées.

FirstSP:
If Exists
   Call SecondSP (UpdateProc)
Else
   Call ThirdSP (InsertProc)

maintenant, je ne suis pas mon propre conseil très souvent, alors prenez - le avec un grain de sel.

faites un select, si vous obtenez un résultat, mettez-le à jour, sinon, créez-le.