Como Realizar um Upsert no SQL Server: Combinando Operações de Inserção e Atualização

Ao trabalhar com bancos de dados, frequentemente encontramos situações em que precisamos gerenciar registros que podem ou não já existir. É aqui que entra o conceito de Upsert, permitindo-nos combinar de forma transparente as operações INSERT e UPDATE. Neste post do blog, exploraremos um desafio comum do SQL Server e forneceremos uma solução que implementa a lógica de Upsert de forma eficaz.

O Problema: Gerenciando Atribuições de Trabalho

Imagine que você tem uma visão que lista as atribuições de trabalho, mostrando quem está atribuído a cada trabalho e seu estágio atual. Seu objetivo é criar uma stored procedure que retorne uma contagem de trabalhos para cada membro da equipe em diferentes estágios.

A tentativa inicial envolve inserir contagens em uma tabela temporária. No entanto, um problema chave surge quando você descobre que membros da equipe com trabalhos em mais de um estágio resultam em linhas separadas na sua tabela de resultados. Em vez disso, você precisa de um método que atualize uma linha existente para um membro da equipe ou insira uma nova linha se nenhuma existir.

Aqui está um exemplo simplificado do código que você começou:

DECLARE @ResultTable table 
(
  StaffName nvarchar(100),
  Stage1Count int,
  Stage2Count int
)

INSERT INTO @ResultTable (StaffName, Stage1Count)
  SELECT StaffName, COUNT(*) FROM ViewJob
  WHERE InStage1 = 1
  GROUP BY StaffName

INSERT INTO @ResultTable (StaffName, Stage2Count)
  SELECT StaffName, COUNT(*) FROM ViewJob
  WHERE InStage2 = 1
  GROUP BY StaffName

Neste trecho, um membro da equipe com trabalhos em ambos os estágios aparece duas vezes na @ResultTable, o que não é o resultado desejado.

A Solução: Realizando um Upsert

Para resolver esse problema, precisamos implementar uma abordagem de Upsert que combine tanto a inserção quanto a atualização dos registros dentro da @ResultTable. Aqui está um passo a passo de como fazer isso:

Passo 1: Inicializar a Tabela de Resultados com Todos os Funcionários

Comece inserindo todos os membros da equipe na sua tabela de resultados com contagens iniciais definidas como zero. Isso garante que cada membro da equipe esteja representado na tabela.

INSERT INTO @ResultTable (StaffName, Stage1Count, Stage2Count)
SELECT StaffName, 0, 0 FROM ViewJob
GROUP BY StaffName

Passo 2: Atualizar as Contagens dos Estágios

Em seguida, precisamos atualizar as contagens para cada estágio. Execute os seguintes comandos SQL, que utilizam a instrução UPDATE para definir os valores com base nas contagens de trabalho da sua visão:

Atualizar Contagem do Estágio 1

UPDATE @ResultTable
SET Stage1Count = (
  SELECT COUNT(*) FROM ViewJob
  WHERE InStage1 = 1 AND @ResultTable.StaffName = StaffName
)

Atualizar Contagem do Estágio 2

UPDATE @ResultTable
SET Stage2Count = (
  SELECT COUNT(*) FROM ViewJob
  WHERE InStage2 = 1 AND @ResultTable.StaffName = StaffName
)

Conclusão

Seguindo esses passos, você alcança uma operação de Upsert eficaz no SQL Server sem precisar recorrer a cursores ou iterações complexas. Este método permite gerenciar e manter de forma precisa as contagens de trabalhos para cada membro da equipe em diferentes estágios no seu banco de dados.

Considerações Finais

Utilizar a metodologia Upsert não apenas melhora a eficiência das suas consultas SQL, mas também simplifica seus esforços de gerenciamento de banco de dados. A representação clara das atribuições de funcionários melhorará significativamente a análise de dados e os resultados de relatórios.

Se você tiver alguma dúvida ou precisar de mais assistência com suas consultas SQL, sinta-se à vontade para comentar abaixo!