Soluzioni per INSERT o UPDATE su SQL Server

Assumi una struttura di tabella di MyTable(KEY, datafield1, datafield2...) .

Spesso voglio aggiornare un record esistente o inserire un nuovo record se non esiste.

Essenzialmente:

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

Qual è il modo migliore per scrivere questo?

non dimenticare le transazioni. Le prestazioni sono buone, ma l’approccio semplice (SE ESISTE ..) è molto pericoloso.
Quando più thread tentano di eseguire Insert-or-update, è ansible ottenere facilmente la violazione della chiave primaria.

Le soluzioni fornite da @Beau Crawford & @Esteban mostrano un’idea generale ma soggetta a errori.

Per evitare deadlock e violazioni PK puoi usare qualcosa del genere:

 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 

o

 begin tran update table with (serializable) set ... where key = @key if @@rowcount = 0 begin insert into table (key, ...) values (@key,..) end commit tran 

Vedi la mia risposta dettagliata a una domanda precedente molto simile

@Beau Crawford è un buon modo in SQL 2005 e in basso, anche se se stai concedendo un rep, dovrebbe andare dal primo a SO . L’unico problema è che per gli inserimenti ci sono ancora due operazioni IO.

MS Sql2008 introduce l’ merge dallo 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, ... ) 

Ora è davvero solo una operazione IO, ma un codice terribile 🙁

Fai un UPSERT:

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

 IF @@ ROWCOUNT = 0
    INSERISCI I VALORI MyTable (FieldA) (@FieldA)

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

Molte persone ti suggeriranno di usare MERGE , ma ti avverto contro di esso. Per impostazione predefinita, non ti protegge dalla concorrenza e dalle condizioni di gara più di più dichiarazioni, ma introduce altri pericoli:

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

Anche con questa syntax “più semplice” disponibile, preferisco ancora questo approccio (gestione degli errori omessa per brevità):

 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; 

Un sacco di gente suggerirà in questo modo:

 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; 

Ma tutto ciò ha successo è assicurarsi che potrebbe essere necessario leggere la tabella due volte per individuare la / e riga / e da aggiornare. Nel primo esempio, avrai solo bisogno di localizzare la / e riga / e una volta. (In entrambi i casi, se non vengono trovate righe dalla lettura iniziale, si verifica un inserimento.)

Altri suggeriranno in questo modo:

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

Tuttavia, questo è problematico se non altro perché lasciare che SQL Server rilevi eccezioni che avresti potuto prevenire in primo luogo è molto più costoso, tranne nel raro scenario in cui quasi ogni inserto fallisce. Lo provo tanto qui:

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

Modificare:

Ahimè, anche a mio discapito, devo ammettere che le soluzioni che lo fanno senza una selezione sembrano essere migliori dal momento che eseguono il compito con un passo in meno.

Se si desidera UPSERT più di un record alla volta, è ansible utilizzare l’istruzione 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 ...]) 

Scopri Mimando l’istruzione MERGE in SQL Server 2005 .

Anche se è piuttosto tardi per commentare questo voglio aggiungere un esempio più completo usando MERGE.

Tali istruzioni Insert + Update vengono in genere denominate istruzioni “Upsert” e possono essere implementate utilizzando MERGE in SQL Server.

Un esempio molto buono è dato qui: http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx

Quanto sopra spiega anche gli scenari di blocco e di concorrenza.

Citerò lo stesso per riferimento:

 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 

Sostituisci i nomi delle tabelle e dei campi in base alle tue esigenze. Prenditi cura dell’uso della condizione ON . Quindi imposta il valore appropriato (e digita) per le variabili sulla riga DECLARE.

Saluti.

È ansible utilizzare l’istruzione MERGE , Questa istruzione viene utilizzata per inserire dati se non esistono o aggiornare se esiste.

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

In SQL Server 2008 è ansible utilizzare l’istruzione MERGE

Se si passa al percorso UPDATE if-no-rows, quindi INSERT, prendere in considerazione prima l’INSERT per evitare una condizione di competizione (supponendo che non sia presente alcun DELETE)

 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 

Oltre ad evitare una condizione di competizione, se nella maggior parte dei casi il record esiste già, ciò causerà il fallimento di INSERT, sprecando CPU.

L’utilizzo di MERGE è probabilmente preferibile per SQL2008 in poi.

Dipende dal modello di utilizzo. Uno deve guardare l’immagine grande di utilizzo senza perdersi nei dettagli. Ad esempio, se il modello di utilizzo è il 99% di aggiornamenti dopo la creazione del record, allora “UPSERT” è la soluzione migliore.

Dopo il primo inserimento (hit), saranno tutti gli aggiornamenti di singola istruzione, senza se e senza ma. La condizione “dove” sull’inserto è necessaria altrimenti inserirà i duplicati e non si desidera gestire il blocco.

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

MS SQL Server 2008 introduce la dichiarazione MERGE, che credo sia parte dello standard SQL: 2003. Come molti hanno dimostrato, non è un grosso problema gestire i casi di una riga, ma quando si hanno a che fare con dataset di grandi dimensioni, è necessario un cursore, con tutti i problemi di prestazioni che si presentano. La dichiarazione MERGE sarà aggiunta molto apprezzata quando si tratta di dataset di grandi dimensioni.

Prima che tutti salgano a HOLDLOCK per paura di questi utenti naif che eseguono direttamente i tuoi sproc 🙂 lasciatemi sottolineare che devi garantire l’unicità dei nuovi PK-s in base alla progettazione (chiavi di identity framework, generatori di sequenze in Oracle, indici univoci per ID esterni, query coperte da indici). Questo è l’alfa e l’omega del problema. Se non lo hai, nessun HOLDLOCK-s dell’universo ti salverà e se lo hai, allora non avrai bisogno di nulla oltre a UPDLOCK alla prima selezione (o di usare prima l’aggiornamento).

Sprocs normalmente funziona in condizioni controllate e con l’assunzione di un chiamante di fiducia (mid-tier). Il che significa che se un semplice pattern di upsert (update + insert o merge) vede mai un PK duplicato che significa un bug nel tuo mid-tier o design di tabella ed è buono che SQL urlerà un errore in questo caso e rifiuterà il record. Posizionare un HOLDLOCK in questo caso equivale a mangiare eccezioni e ad accettare dati potenzialmente difettosi, oltre a ridurre il tuo perf.

Detto questo, usando MERGE, o UPDATE, INSERT è più facile sul server e meno incline agli errori poiché non devi ricordarti di aggiungere (UPDLOCK) per selezionare prima. Inoltre, se si eseguono inserimenti / aggiornamenti in piccoli gruppi, è necessario conoscere i dati per decidere se una transazione è appropriata o meno. È solo una raccolta di record non correlati, quindi una transazione “avvolgente” aggiuntiva sarà dannosa.

Le condizioni di gara sono davvero importanti se provi prima un aggiornamento seguito da un inserto? Diciamo che hai due thread che vogliono impostare un valore per la chiave :

Discussione 1: valore = 1
Thread 2: value = 2

Esempio di condizioni di gara

  1. la chiave non è definita
  2. Thread 1 non riesce con l’aggiornamento
  3. Thread 2 non riesce con l’aggiornamento
  4. Esattamente uno di thread 1 o thread 2 succede con insert. Ad esempio il thread 1
  5. L’altro thread fallisce con insert (con chiave duplicata di errore) – thread 2.

    • Risultato: il “primo” dei due gradini da inserire, decide il valore.
    • Risultato desiderato: l’ultimo dei 2 thread per scrivere i dati (aggiornamento o inserimento) dovrebbe decidere il valore

Ma; in un ambiente con multithreading, lo scheduler del SO decide sull’ordine dell’esecuzione del thread: nello scenario sopra, dove abbiamo questa condizione di competizione, era il sistema operativo che decideva la sequenza di esecuzione. Vale a dire: è sbagliato dire che “thread 1” o “thread 2” era “primo” da un punto di vista di sistema.

Quando il tempo di esecuzione è così vicino per il thread 1 e il thread 2, l’esito della condizione di competizione non ha importanza. L’unico requisito dovrebbe essere che uno dei thread debba definire il valore risultante.

Per l’implementazione: se l’aggiornamento seguito dall’inserzione risulta nell’errore “chiave duplicata”, questo dovrebbe essere considerato come successo.

Inoltre, non bisogna mai presumere che il valore nel database sia lo stesso del valore che hai scritto per ultimo.

Avevo provato sotto la soluzione e funziona per me, quando si verifica una richiesta simultanea di inserimento.

 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 

Puoi usare questa query. Funziona in tutte le edizioni di SQL Server. È semplice e chiaro. Ma è necessario utilizzare 2 query. È ansible utilizzare se non è ansible utilizzare 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 

NOTA: si prega di spiegare i negativi di risposta

Se si utilizza ADO.NET, DataAdapter gestisce questo.

Se vuoi gestirlo da solo, questo è il modo:

Assicurati che ci sia un vincolo di chiave primaria sulla tua colonna chiave.

Allora lei:

  1. Fai l’aggiornamento
  2. Se l’aggiornamento non riesce perché esiste già un record con la chiave, fare l’inserto. Se l’aggiornamento non fallisce, hai finito.

Puoi anche farlo al contrario, cioè prima inserisci l’inserto e fai l’aggiornamento se l’inserimento fallisce. Normalmente il primo modo è migliore, perché gli aggiornamenti vengono eseguiti più spesso degli inserti.

Fare un if esiste … altrimenti … implica fare due richieste minime (una da controllare, una da agire). Il seguente approccio richiede solo uno in cui esiste il record, due se è richiesto un inserto:

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

Di solito faccio quello che molti degli altri poster hanno detto riguardo al fatto di verificarne l’esistenza prima e poi di fare qualunque sia il percorso corretto. Una cosa che dovresti ricordare quando fai questo è che il piano di esecuzione memorizzato nella cache da sql potrebbe non essere ottimale per un percorso o l’altro. Credo che il modo migliore per farlo sia chiamare due diverse stored procedure.

 FirstSP:
 Se esiste
    Chiama SecondSP (UpdateProc)
 Altro
    Chiama ThirdSP (InsertProc)

Ora, non seguo il mio stesso consiglio molto spesso, quindi prendilo con un pizzico di sale.

Fai una selezione, se ottieni un risultato, aggiornalo, altrimenti, crealo.