SQL Server OUTPUT 절

오늘 소개해 드릴 a tiny tip은 OUTPUT 절 입니다.

OUTPUT “절”로 말씀 드린 것처럼 프로시저나 동적 쿼리의 OUTPUT과는 다른 구문입니다.

이미 알고 계신다면 복습차원으로 봐주세요~

 

BOL (http://technet.microsoft.com/ko-kr/library/ms177564(v=sql.105).aspx)에선 OUTPUT 절을

INSERT, UPDATE, DELETE 또는 MERGE 문의 영향을 받는 각 행의 정보 또는 각 행을 기반으로 하는 식을 반환합니다.

라고 하는데요. 이해하기 쉽게 다음 예제를 통해 알아보겠습니다.

 

--테스트 테이블 생성

CREATE TABLE dbo.Output_Test (

           seq int not null identity(1,1) primary key,

           name nvarchar(10) not null,

           title nvarchar(10) not null)

          

--Output 절을 이용한 INSERT

INSERT INTO dbo.Output_Test (name, title)

OUTPUT INSERTED.seq, INSERTED.name, INSERTED.title --INSERT 된 행의 컬럼을 출력

VALUES ('김해', '팀장'),

           ('원미', '파트장'),

           ('백승', '팀원'),

           ('서일', '팀원')

----------------------------------------------------------------------

 

위 결과와 같이 데이터 입력과 동시에 적용된 데이터를 확인 가능합니다.

 

--Output 절을이용한DELETE

DELETE FROM dbo.Output_Test

OUTPUT DELETED.seq, DELETED.name --DELETE 된행의컬럼을출력

WHERE name = '서일'

----------------------------------------------------------------------

 

SELECT seq, name, title

FROM dbo.Output_Test

----------------------------------------------------------------------

삭제 시에도 영향을 받은 데이터를 출력할 수 있습니다.

 

--Output 절을이용한UPDATE

UPDATE dbo.Output_Test

SET title = '사장'

OUTPUT INSERTED.name, DELETED.title as before, INSERTED.title as after

WHERE name = '백승'

----------------------------------------------------------------------

 

SELECT seq, name, title

FROM dbo.Output_Test

업데이트 문에서는 Inserted, Deleted 접두사를 이용해 before & after 데이터를 한번에 확인 가능합니다.

 

OUTPUT INTO 절을 사용하면, 영향을 받는 데이터가 동시에 테이블로 복사 됩니다.

--로그테이블생성

CREATE TABLE dbo.Output_Test_LOG (

           seq int not null primary key,

           name nvarchar(10) not null,

           title nvarchar(10) not null,

           kind nvarchar(10) not null)

          

DELETE FROM dbo.Output_Test

OUTPUT DELETED.*, 'DELETE' as kind INTO dbo.Output_Test_LOG

--DELETE 된행을로그테이블로복사

WHERE name in ('김해', '원미')

 

SELECT seq, name, title, kind

FROM dbo.Output_Test_LOG

----------------------------------------------------------------------

위 결과와 같이 삭제된 행이 로그테이블로 복사된 것을 확인할 수 있습니다.

 

제약사항이나 더 자세한 사항은 BOL 참고하시면 좋을 것 같습니다.

OUTPUT 절이 유용하게 쓰일 수 있는 곳을 생각나는 대로 나열하자면,

Auto increment하게 증가되는 값이 있다면, INSERT 후 입력된 값을 알기 위해 SELECT 없이 반환 받을 수 있습니다.

응용 프로그램에서 데이터 처리 후 처리된 값을 반환 받아야 될 때도 마찬가지 입니다.

또한 테이블로의 INTO 절이 가능하기 때문에 로그 데이터 처리 시에도 유용하다고 판단됩니다.