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

OUTPUT

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

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

 

Insert 문 사용 시 Output 

INSERT OUTPUT INTO

insert into TABLE_A (a_column1, a_column2, a_column3 ...)
-- output 구문
output inserted.a_column1, inserted.a_column2, inserted.a_column3 ...
-- into 구문
  into TABLE_B (b_column1, b_column2, b_column3 ...)
values (value1, value2, value3 ...) -- 혹은 select 문

insert 문 사용시, insert 와 values 혹은 select 사이에 output, into 구문을 사용한다.

 

inserted 접두사

Insert Output 문에서는 output 시 inserted 라는 접두사의 컬럼을 사용한다.

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

 

Insert 문에서는 삽입만 이루어 지기 때문에 inseted 접두사만 사용한다.

 

output 구문

실행 쿼리 1

insert into TABLE_A (a_column1, a_column2, a_column3 ...)
-- output 구문
output inserted.a_column1, inserted.a_column2, inserted.a_column3 ...
values (value1, value2, value3 ...) -- 혹은 select 문

output 구문은 이렇게 insert 와 values 사이에 위치하게 된다.

Insert 문에서 output 구문을 단독으로 사용시 삽입 된 행들이 바로 반환된다.

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

반환 값 1

반응형

한 행만 반환되는 것이 아니라,

여러 행이 들어있는 TABLE을 이용하여 여러 행을 반환 할 경우 다 같이 반환됨을 알 수 있다.

실행 쿼리 2

TABLE_B의 3개의 행을 타겟 TABLE_A의 insert 후 3개의 행이 아래와 같이 반환 된다.

반환 값 2

 

into 구문

insert into TABLE_A (a_column1, a_column2, a_column3 ...)
-- output 구문
output inserted.a_column1, inserted.a_column2, inserted.a_column3 ...
-- into 구문
  into TABLE_B (b_column1, b_column2, b_column3 ...)
values (value1, value2, value3 ...) -- 혹은 select 문

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

타겟이 되는 TABLE_A, 반환 값을 삽입하는 TABLE_B의 전체 작업이 하나의 원자성을 가지게 되며,

하나의 트랜잭션으로 실행된다.

 

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

 

구문의 특징 및 주의사항

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

 

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

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

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

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

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

 

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

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

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

Related Posts

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

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

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

 

 

Reference

MsSQL - OUTPUT 절(Transact-SQL)