[MsSQL] OUTPUT - Update 사용 시, 변경 전/후 행 반환하기 (deleted/inserted)

OUTPUT

Output 구문은 insert, update, delete, merge 문과 함께 사용되며, 각 DML문에 처리되는 행을 반환하는 문법이다.

각 행을 반환하여, Into 구문를 통해 TABLE 혹은 TEMP TABLE 에 해당 행을 삽입한다.

 

Update 문 사용 시 Output 

  update TABLE_A
     set a_column1 = 'value3'
  -- output 구문
  output deleted.a_column1, inserted.a_column2, deleted.a_column3 + inserted.a_column3
  -- into 구문
    into TABLE_B (b_column1, b_column2, b_column3)
   where [조건]

 

update문 사용시, update ~set과 조건 문 사이에 output, into 구문이 위치한다.

 

deleted 접두사

Update Output 문에는 output 시, deleted 라는 접두사와 아래 설명할 inserted라는 접두사를 사용 할 수 있다.

 

deleted 접두사는 타겟이 되는 TABLE_A의 row의 변경 전 혹은 삭제가 될 경우 해당 행을 반환하는 접두사이다.

 

Update 문에서는 해당 row의 컬럼이 변경 되므로 변경 전 행이 반환된다.

변경되어 사라지기에 deleted 라고 생각하면 조금 더 이해하기 편하다.

 

inserted 접두사

Update Output 문에서 output 시, deleted라는 접두사 뿐만 아니라 inserted라는 접두사도 사용이 가능하다

 

inserted 접두사는 타겟이 되는 TABLE_A의 row의 변경 후 혹은 삽입이 될 경우 해당 행을 반환하는 접두사이다.

 

위에 설명한 것과 마찬가지의 개념으로 Update 문에서는 해당 row의 컬럼이 변경 되므로 변경 후 행이 반환된다.

변경되어 새롭게 바뀌어 추가 되기에 inserted라고 생각하면 조금 더 이해하기 편하다.

 

output 구문

실행 쿼리 1

  update TABLE_A
     set a_column1 = 'value3'
  -- output 구문
  output deleted.a_column1, inserted.a_column2, deleted.a_column3 + inserted.a_column3
   where [조건]

output 구문은 이렇게 update ~ set과  조건문 사이에 위치하게 된다.

update 문에서 output 구문을 단독으로 사용 시,

변경 전 행들은 deleted, 변경 후 행들은 inserted 접두사를 통해 output 구문으로 반환된다.

 

위 사진의 쿼리를 실행시 아래와 같이 반환됨을 알 수 있다.

반환 값 1

여기서 중요한 것은 deleted / inserted를 혼합하여 사용 할 수 있다는 점인데,

두개를 활용하여 변경 전 / 후의 연산으로 값을 비교한다던지의 활용을 할 수 있다.

 

조건문이 없거나, 조건에 해당하는 값이 여러개 일 경우 한 행만 반환되는 것이 아니라, 삭제된 모든 행이 반환된다.

실행 쿼리 2

조건문 없이 TABLE_A의 모든 행을 변경 할 경우 아래와 같이 TABLE_A의 변경 된 모든 값들을 반환한다.

into 구문

  update TABLE_A
     set a_column1 = 'value3'
  -- output 구문
  output deleted.a_column1, inserted.a_column2, deleted.a_column3 + inserted.a_column3
  -- into 구문
    into TABLE_B (b_column1, b_column2, b_column3)
   where [조건]

반응형

output 구문을 통해 반환되는 값들을 into 구문을 통해 특정 TABLE 혹은 TEMP TABLE 에 해당 행을 삽입한다.

타겟이 되는 TABLE_A, 반환 값을 삽입하는 TABLE_B의 작업이 원자성을 가지어, 하나의 트랜잭션으로 실행된다.

 

TABLE_A의 작업이 실패하거나, TABLE_B의 삽입이 실패할 경우 이 트랜잭션이 원자성으로 인해 rollback처리가 된다.

 

이렇게 하나의 트랜잭션으로 묶어 두개의 테이블에 동시에 작업을 진행 할 수 있는데,

update문을 구성할 때 output, into 구문을 사용할 경우 아래와 같은 작업에 효과적으로 사용할수 있다.

 

특정 컬럼의 변경 전/후를 저장하여 특정 테이블에 로그로 남긴다거나,

컬럼의 전/후 비교값을 계산하여 특정 테이블에 담아두는 등의 작업을 쉽게 진행 할 수 있다.

 

구문의 특징 및 주의사항

output과 into 구문을 사용 할 경우, 여러 가지 특징 들이 있다.

 

output 구문에는 서브쿼리가 들어갈 수 없다. / 변수 값을 사용 할 수 있다.

into 구문의 TABLE_B에 output 구문으로 반환되는 값과

다른 테이블의 값을 동시에 넣고 싶어 서브쿼리를 사용할 경우 에러가 발생한다.

위와 같은 경우, TABLE_C의 값을 변수에 담아 변수를 활용하여 output 절을 사용 함으로 해결한다.

변수를 선언 하여 해당 변수에 값을 사용한다.

 

into 구문의 TABLE에는 트리거가 있으면 안된다.

TABLE_B에 트리거를 생성한 상태이다.

트리거가 설정되어있는 테이블을 into 구문의 TABLE로 사용할 경우 아래와 같은 에러가 발생한다.

 

Related Posts

[Database] 트랜잭션(Transaction)과 격리 수준(Isolation Level)

[MsSQL] OUTPUT - Insert 사용 시, 삽입 된 행 반환하기 (Inserted)

[MsSQL] OUTPUT - Delete 사용 시, 삭제 된 행 반환하기 (deleted)

 

Reference

MsSQL - OUTPUT 절(Transact-SQL)