SQL Server의 OUTPUT 절: 데이터 변경 작업의 결과 추적 및 활용

 

sql 튜닝 관련 강의를 듣다가 OUTPUT절에 대해서 뒤늦게 알게 되었다. 

DELETE, UPDATE, INSERT 작업 뒤에 바로 해당 컬럼을 조회 할 수 있는 좋은 기능이라 이에 대해 알아보았다.

 

1.개요

OUTPUT 절은 INSERT, UPDATE 및 DELETE 문에서 사용할 수 있다.

이는 데이터 변경 작업의 결과를 반환하는 데 사용된다. 이를 통해 변경된 데이터의 상태를 확인하거나 다른 작업에 활용할 수 있다.

 

2.구문

INSERT

INSERT INTO 테이블명 (컬럼1, 컬럼2, ...)
OUTPUT inserted.컬럼1, inserted.컬럼2, ...
VALUES (값1, 값2, ...)

 

DELETE

DELETE FROM 테이블명
OUTPUT deleted.컬럼1, deleted.컬럼2, ...
WHERE 조건

 

UPDATE

UPDATE 테이블명
SET 컬럼1 = 값1, 컬럼2 = 값2, ...
OUTPUT inserted.컬럼1, inserted.컬럼2, ...
WHERE 조건

 

위 구문에서 'inserted'는  INSERT 또는 UPDATE 문에서 새로 추가되거나 변경된 데이터를 나타내며, 'deleted'는 UPDATE 또는 DELETE 문에서 변경되거나 삭제된 데이터를 나타낸다. 이러한 'inserted'와 'deleted'문을 통해 변경된 데이터에 접근할 수 있다.

 

3.활용

데이터 변경 이력 추적

OUTPUT 절을 사용하여 변경된 데이터의 이력을 추적할 수 있다. 예를 들면, UPDATE 문에서 변경 전/후의 데이터 값을 비교하여 로그 테이블에 저장하거나, 변경된 데이터를 다른 테이블에 삽입할 수 있다.

예시 : Employees 테이블에서 Salary를 10% 증가시키고, 변경된 데이터인 EmployeeID, Salary, 그리고 변경 일자를 SalaryChangeLog 테이블에 저장한다

UPDATE Employees
SET Salary = Salary * 1.1
OUTPUT inserted.EmployeeID, inserted.Salary, GETDATE() AS ChangeDate INTO SalaryChangeLog;

 

 

변경된 데이터 반환

OUTPUT 절을 통해 변경된 데이터의 일부 또는 전체를 반환할 수 있다. 이는 복잡한 작업에서 특정 작업을 통과한 데이터를 추출하거나, 변경 작업의 결과를 다른 애플리케이션에 전달할 때 유용하다.

예시 : Products 테이블에서 Price를 5% 증가시키고, 변경된 데이터의 ProductID와 증가시킨 Price를 임시 테이블인 @UpdatedData에 저장하고 반환.

DECLARE @UpdatedData TABLE (
    ProductID INT,
    NewPrice DECIMAL(10, 2)
);

UPDATE Products
SET Price = Price * 1.05
OUTPUT inserted.ProductID, inserted.Price INTO @UpdatedData;

SELECT * FROM @UpdatedData;

 

대량 작업 처리

OUTPUT 절을 활용하여 대량의 데이터 변경 작업을 수행하고, 변경된 데이터의 세부 정보를 추적할 수 있다. 이는 대량 INSERT, UPDATE 또는 DELETE 작업을 수행할 때 작업의 안정성과 추적성을 보장하는 데 도움을 줄 수 있다.

예시 : Orders 테이블에서 6개월 이전의 주문을 삭제하고, 삭제된 주문의 OrderID와 CustomerID를 DeletedOrdersLog 테이블에 저장.

DELETE FROM Orders
OUTPUT deleted.OrderID, deleted.CustomerID INTO DeletedOrdersLog
WHERE OrderDate < DATEADD(MONTH, -6, GETDATE());

 

OUTPUT절은 SQL Server 2005 버전 이후 부터 사용이 가능하다. INSERT나 UPDATE를 할 때 마다 해당 데이터를 반환 받기위해서 따로 쿼리를 또 만들어 줬어야 했는데, 해당 기능을 사용하니 쿼리문도 줄어서 활용하기 좋아 보인다.

DB 로그를 쌓기도 좋아보이고, 해당 기능을 잘 활용해서 개발해보자.