Soluções para INSERT OR UPDATE no SQL Server

Suponha uma estrutura de tabela de MyTable(KEY, datafield1, datafield2...) .

Muitas vezes, quero atualizar um registro existente ou inserir um novo registro, se ele não existir.

Essencialmente:

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

Qual é a melhor maneira de escrever isso?

não se esqueça das transactions. O desempenho é bom, mas a abordagem simples (SE EXISTE ..) é muito perigosa.
Quando vários encadeamentos tentarem executar Insert-or-update, você poderá obter facilmente violação de chave primária.

As soluções fornecidas pela @Beau Crawford & @Esteban mostram uma ideia geral, mas propensa a erros.

Para evitar impasses e violações de PK, você pode usar algo assim:

 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 

Veja minha resposta detalhada a uma pergunta anterior muito semelhante

O @Beau Crawford’s é uma boa maneira no SQL 2005 e abaixo, embora se você está concedendo o representante, ele deve ir para o primeiro sujeito para SO . O único problema é que, para inserções, ainda são duas operações de IO.

O MS Sql2008 introduz a merge do padrão 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, ... ) 

Agora é realmente apenas uma operação de IO, mas um código horrível 🙁

Faça um UPSERT:

 ATUALIZAÇÃO MyTable SET FieldA = @ FieldA WHERE Key = @ Key

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

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

Muitas pessoas vão sugerir que você use MERGE , mas eu o aviso contra isso. Por padrão, ele não protege você contra condições de simultaneidade e corrida mais do que múltiplas declarações, mas apresenta outros perigos:

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

Mesmo com essa syntax “mais simples” disponível, ainda prefiro essa abordagem (tratamento de erros omitido por brevidade):

 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; 

Muita gente vai sugerir desta maneira:

 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; 

Mas tudo isso é garantir que você precise ler a tabela duas vezes para localizar a (s) linha (s) a ser atualizada (s). No primeiro exemplo, você só precisará localizar a (s) linha (s) uma vez. (Em ambos os casos, se nenhuma linha for encontrada na leitura inicial, ocorre uma inserção).

Outros irão sugerir desta maneira:

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

No entanto, isso é problemático se, por nenhum outro motivo, permitir que o SQL Server detecte exceções que você poderia ter evitado, em primeiro lugar, é muito mais caro, exceto no cenário raro em que quase todas as inserções falham. Eu provo tanto aqui:

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

Editar:

Infelizmente, mesmo em meu detrimento, devo admitir que as soluções que fazem isso sem um seleto parecem ser melhores, já que elas cumprem a tarefa com um passo a menos.

Se você quiser UPSERT mais de um registro de cada vez, você pode usar a instrução MERGE ANSI SQL: 2003 DML.

 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 ...]) 

Confira imitando a instrução MERGE no SQL Server 2005 .

Embora seja muito tarde para comentar sobre isso, quero adicionar um exemplo mais completo usando MERGE.

Essas instruções Insert + Update geralmente são chamadas de instruções “Upsert” e podem ser implementadas usando MERGE no SQL Server.

Um bom exemplo é dado aqui: http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx

O acima explica os cenários de bloqueio e simultaneidade também.

Eu vou estar citando o mesmo para referência:

 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 

Substitua os nomes de tabelas e campos pelo que você precisar. Cuide do uso da condição ON . Em seguida, defina o valor apropriado (e digite) para as variables ​​na linha DECLARE.

Felicidades.

Você pode usar a instrução MERGE , essa instrução é usada para inserir dados se não existir ou atualizar se existir.

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

No SQL Server 2008, você pode usar a instrução MERGE

Se a rota UPDATE if-no-rows-updated for INSERT, considere fazer o INSERT primeiro para evitar uma condição de corrida (assumindo que não há intervenção 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 

Além de evitar uma condição de corrida, se na maioria dos casos o registro já existir, isso fará com que o INSERT falhe, desperdiçando a CPU.

Usando MERGE provavelmente preferível para SQL2008 em diante.

Isso depende do padrão de uso. Um tem que olhar para o uso grande figura sem se perder nos detalhes. Por exemplo, se o padrão de uso for 99% de atualizações depois que o registro for criado, o ‘UPSERT’ será a melhor solução.

Após a primeira inserção (hit), serão todas as atualizações de instruções únicas, sem ifs ou buts. A condição ‘where’ na inserção é necessária, caso contrário, ela irá inserir duplicatas e você não deseja lidar com o bloqueio.

 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 

O MS SQL Server 2008 introduz a instrução MERGE, que, acredito, faz parte do padrão SQL: 2003. Como muitos demonstraram, não é um grande problema lidar com casos de uma linha, mas quando se lida com grandes conjuntos de dados, é necessário um cursor, com todos os problemas de desempenho que surgem. A instrução MERGE será muito bem recebida quando se tratar de grandes conjuntos de dados.

Antes de todo mundo pular para o HOLDLOCK-s por medo desses usuários na- vesiosos executando seus sprocs diretamente 🙂 deixe-me salientar que você tem que garantir a unicidade dos novos PK-s pelo design (chaves de identidade, geradores de seqüência no Oracle, índices exclusivos para IDs externos, consultas cobertas por índices). Esse é o alfa e o ômega do problema. Se você não tem isso, nenhum HOLDLOCK-s do universo vai salvá-lo e se você tiver isso, então você não precisa de nada além de UPDLOCK no primeiro select (ou para usar a atualização primeiro).

Normalmente, as sprocs são executadas sob condições muito controladas e com a suposição de um chamador confiável (nível intermediário). O que significa que se um padrão simples de upsert (update + insert ou merge) puder ver um PK duplicado que signifique um erro no design de tabela ou mid-tier e é bom que o SQL grite uma falha nesse caso e rejeite o registro. Colocar um HOLDLOCK nesse caso equivale a comer exceções e a coletar dados potencialmente defeituosos, além de reduzir seu desempenho.

Dito isto, Usando MERGE ou UPDATE, o comando INSERT é mais fácil em seu servidor e menos propenso a erros, já que você não precisa se lembrar de adicionar (UPDLOCK) para selecionar primeiro. Além disso, se você estiver fazendo inserções / atualizações em pequenos lotes, precisará saber seus dados para decidir se uma transação é apropriada ou não. É apenas uma coleção de registros não relacionados, então a transação “envolvente” adicional será prejudicial.

As condições de corrida realmente importam se você tentar primeiro uma atualização seguida de uma inserção? Vamos dizer que você tem dois tópicos que querem definir um valor para a chave :

Tópico 1: value = 1
Tópico 2: valor = 2

Exemplo de cenário de condição de corrida

  1. chave não está definida
  2. Thread 1 falha com atualização
  3. Thread 2 falha com atualização
  4. Exatamente um do segmento 1 ou segmento 2 é bem-sucedido com a inserção. Por exemplo, thread 1
  5. O outro segmento falha com a inserção (com chave duplicada de erro) – segmento 2.

    • Resultado: O “primeiro” dos dois degraus para inserir, decide o valor.
    • Resultado desejado: O último dos dois segmentos para gravar dados (atualizar ou inserir) deve decidir o valor

Mas; em um ambiente multiencadeado, o planejador do SO decide a ordem da execução do encadeamento – no cenário acima, onde temos essa condição de corrida, foi o sistema operacional que decidiu a sequência de execução. Ou seja: é errado dizer que “thread 1” ou “thread 2” foi “primeiro” de um ponto de vista do sistema.

Quando o tempo de execução está tão próximo do encadeamento 1 e do encadeamento 2, o resultado da condição de corrida não importa. O único requisito deve ser que um dos encadeamentos defina o valor resultante.

Para a implementação: Se a atualização seguida de inserção resultar em erro “chave duplicada”, isso deve ser considerado um sucesso.

Além disso, nunca se deve assumir que o valor no database é o mesmo que o valor que você escreveu por último.

Eu tentei abaixo da solução e funciona para mim, quando ocorre a solicitação simultânea para instrução de inserção.

 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 

Você pode usar essa consulta. Trabalhe em todas as edições do SQL Server. É simples e claro. Mas você precisa usar 2 consultas. Você pode usar se você não pode usar 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: Por favor, explique os negativos da resposta

Se você usa o ADO.NET, o DataAdapter manipula isso.

Se você quiser lidar com isso sozinho, é assim:

Certifique-se de que haja uma restrição de chave primária em sua coluna-chave.

Então você:

  1. Faça a atualização
  2. Se a atualização falhar porque já existe um registro com a chave, faça a inserção. Se a atualização não falhar, você está acabado.

Você também pode fazer o inverso, ou seja, fazer a inserção primeiro e fazer a atualização se a inserção falhar. Normalmente, a primeira maneira é melhor, porque as atualizações são feitas com mais frequência do que as inserções.

Fazer um if existe … else … envolve fazer duas solicitações no mínimo (uma para verificar, uma para executar uma ação). A abordagem a seguir requer apenas um em que o registro existe, dois se uma inserção for necessária:

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

Eu costumo fazer o que vários dos outros cartazes disseram a respeito de verificar se ele existe primeiro e depois fazer qualquer que seja o caminho correto. Uma coisa que você deve lembrar ao fazer isso é que o plano de execução armazenado em cache pelo sql pode não ser ideal para um caminho ou outro. Eu acredito que a melhor maneira de fazer isso é chamar dois stored procedures diferentes.

 FirstSP:
 Se existe
    Chamar SecondSP (UpdateProc)
 Outro
    Chamar ThirdSP (InsertProc)

Agora, eu não sigo meus próprios conselhos com muita frequência, então tome com um grão de sal.

Faça um select, se você obtiver um resultado, atualize-o, caso contrário, crie-o.