Sql Sql Server T Sql

SQL Server에서 `Upsert` 수행 방법: 삽입 및 업데이트 작업 결합

데이터베이스에서 작업할 때, 이미 존재할 수도 있고 존재하지 않을 수도 있는 레코드를 관리해야 하는 상황을 자주 만납니다. 이때 Upsert 개념이 등장하여 INSERT와 UPDATE 작업을 매끄럽게 결합할 수 있게 해줍니다. 이 블로그 포스트에서는 일반적인 SQL Server 문제를 탐구하고 Upsert 로직을 효과적으로 구현하는 솔루션을 제공하겠습니다.

문제: Job Assignment 관리

각 작업에 누가 배정되었고 현재 단계가 무엇인지 보여주는 작업 배정 목록을 가진 뷰가 있다고 가정해 보겠습니다. 귀하의 목표는 각 스태프가 각각의 단계에서 수행하는 작업 수를 반환하는 저장 프로시저를 만드는 것입니다.

초기 시도에서는 임시 테이블에 카운트를 삽입하는 방식이었습니다. 그러나 여러 단계의 작업을 가진 스태프가 있을 경우 결과 테이블에 별도의 행이 생성되는 주요 문제가 발생합니다. 대신, 스태프에 대한 기존 행을 업데이트하거나 존재하지 않을 경우 새로운 행을 삽입하는 방법이 필요합니다.

다음은 귀하가 시작했던 코드의 간단한 예제입니다:

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

이 코드 조각에서는 두 단계에서 작업을 가진 스태프가 @ResultTable에 두 번 나타나는데, 이는 원하는 결과가 아닙니다.

해결책: Upsert 수행하기

이 문제를 해결하기 위해, @ResultTable 내에서 레코드를 삽입하고 업데이트하는 두 가지 작업을 결합한 Upsert 접근 방식을 구현할 필요가 있습니다. 다음은 이를 수행하는 단계별 설명입니다.

1단계: 모든 스태프와 함께 결과 테이블 초기화

모든 스태프를 결과 테이블에 삽입하고 초기 카운트를 0으로 설정합니다. 이렇게 하면 모든 스태프가 테이블에 나타나게 됩니다.

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

2단계: 단계 카운트 업데이트

다음으로 각 단계에 대한 카운트를 업데이트해야 합니다. 다음 SQL 명령을 실행하여 뷰의 작업 카운트를 기준으로 값을 설정하는 UPDATE 문을 사용합니다:

단계 1 카운트 업데이트

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

단계 2 카운트 업데이트

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

결론

이 단계를 따르면 SQL Server에서 효과적인 Upsert 작업을 달성할 수 있으며, 커서나 복잡한 반복 작업을 사용할 필요가 없습니다. 이 방법은 데이터베이스 내에서 각 스태프의 다양한 단계에 대한 정확한 작업 수를 쉽게 관리하고 유지할 수 있게 도와줍니다.

최종 생각

Upsert 방법론을 활용하면 SQL 쿼리의 효율성이 향상될 뿐만 아니라 데이터베이스 관리 노력을 간소화할 수 있습니다. 스태프 배정의 명확한 표현은 데이터 분석 및 보고 결과를 크게 개선할 것입니다.

SQL 쿼리에 대한 질문이 있거나 추가 지원이 필요하시면 아래 댓글로 언제든지 문의해 주시기 바랍니다!

SQL Server에서 Upsert 수행 방법: 삽입 및 업데이트 작업 결합