end - sql transact case when else




Inserisci Aggiorna stored proc su SQL Server (6)

Ho scritto un proc memorizzato che farà un aggiornamento se esiste un record, altrimenti farà un inserto. Sembra qualcosa del genere:

update myTable set [email protected], [email protected] where [email protected]
if @@rowcount = 0
insert into myTable (Col1, Col2) values (@col1, @col2)

La mia logica dietro la scrittura in questo modo è che l'aggiornamento eseguirà una selezione implicita usando la clausola where e se restituisce 0, allora l'insert avrà luogo.

L'alternativa a farlo in questo modo sarebbe quella di fare una selezione e quindi in base al numero di righe restituite o fare un aggiornamento o inserire. Questo ho considerato inefficiente perché se si deve fare un aggiornamento causerà 2 selezioni (la prima chiamata di selezione esplicita e la seconda implicita nel dove dell'aggiornamento). Se il proc dovesse fare un inserto allora non ci sarebbe differenza di efficienza.

La mia logica suona qui? È così che combinerai un inserto e un aggiornamento in un processo memorizzato?


A proposito, MERGE è una delle nuove funzionalità di SQL Server 2008.


Grande fan di UPSERT, riduce davvero il codice da gestire. Ecco un altro modo per farlo: uno dei parametri di input è ID, se l'ID è NULL o 0, sai che è un INSERT, altrimenti è un aggiornamento. Presume che l'applicazione sappia se esiste un ID, quindi non funzionerà in tutte le situazioni, ma se ne eseguirà la metà verrà interrotto.


La tua logica sembra buona, ma potresti voler aggiungere del codice per impedire l'inserimento se tu fossi passato in una chiave primaria specifica.

In caso contrario, se si esegue sempre un inserimento se l'aggiornamento non ha alcun effetto sui record, cosa succede quando qualcuno cancella il record prima di eseguire "UPSERT"? Ora il record che stavi tentando di aggiornare non esiste, quindi creerà un record. Questo probabilmente non è il comportamento che stavi cercando.


Modificato il post di Dima Malenko:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

BEGIN TRANSACTION UPSERT 

UPDATE MYTABLE 
SET    COL1 = @col1, 
       COL2 = @col2 
WHERE  ID = @ID 

IF @@rowcount = 0 
  BEGIN 
      INSERT INTO MYTABLE 
                  (ID, 
                   COL1, 
                   COL2) 
      VALUES      (@ID, 
                   @col1, 
                   @col2) 
  END 

IF @@Error > 0 
  BEGIN 
      INSERT INTO MYERRORTABLE 
                  (ID, 
                   COL1, 
                   COL2) 
      VALUES      (@ID, 
                   @col1, 
                   @col2) 
  END 

COMMIT TRANSACTION UPSERT 

È possibile intercettare l'errore e inviare il record a una tabella di inserimento non riuscita.
Avevo bisogno di farlo perché stiamo prendendo qualsiasi dato inviato tramite WSDL e, se possibile, riparandolo internamente.


Se non si esegue un'unione in SQL 2008, è necessario modificarlo in:

se @@ rowcount = 0 e @@ error = 0

in caso contrario, se l'aggiornamento fallisce per qualche motivo, proverà a eseguire un inserimento in seguito perché il conteggio delle righe su un'istruzione non riuscita è 0


Se si utilizza con SQL Server 2000/2005, il codice originale deve essere incluso nella transazione per assicurarsi che i dati rimangano coerenti nello scenario concorrente.

BEGIN TRANSACTION Upsert
update myTable set [email protected], [email protected] where [email protected]
if @@rowcount = 0
insert into myTable (Col1, Col2) values (@col1, @col2)
COMMIT TRANSACTION Upsert

Ciò comporterà costi di prestazioni aggiuntivi, ma garantirà l'integrità dei dati.

Aggiungere, come già suggerito, MERGE dovrebbe essere usato dove disponibile.





upsert