[MSSQL] INSERT/UPDATE 문에서 값 사용하기

음. 좀 부끄러운 이야기 입니다만, INSERT/UPDATE 시 트리거에서 즐겨 사용했던, INSERTED, DELETED 테이블(임시?)이 트리거 내부에서 뿐만 아니라 바깥에서 사용될 수 있다는 건 어렴풋이 "안 될 거야 아마." 라고 생각했는데, 필요에 의해 좀 찾아보니, 그냥 사용해도 되는 테이블이였군요.

Implementing the OUTPUT Clause in SQL Server 2008

아아 부끄러워라. 그러게, 애시당초 이게 안 된다는 건 말이 안 되는데 말이죠.

아무튼, 이걸 잘 활용하면 좋은 게, 일괄로 어떤 작업을 할 경우, PK 만 제대로 딴다면, 현재 내가 잡아 놓은 데이터들을 효율적으로 활용할 수 있습니다.

예제 보시죠.

/* 예제 테이블 데이터 생성 */
CREATE TABLE XTEST
(
    idx      INT IDENTITY(1, 1),
    data     VARCHAR(100),
    start_dt DATETIME,
    end_dt   DATETIME,
    stat     VARCHAR(100)
)

INSERT INTO XTEST VALUES ( NEWID(), NULL, NULL, NULL );
INSERT INTO XTEST VALUES ( NEWID(), NULL, NULL, NULL );
INSERT INTO XTEST VALUES ( NEWID(), NULL, NULL, NULL );
INSERT INTO XTEST VALUES ( NEWID(), NULL, NULL, NULL );

/* OUTPUT 받을 테이블 변수 생성 */
DECLARE @output table
(
    idx INT
);

/* 내 작업이다 표시 */
UPDATE XTEST
   SET start_dt = GETDATE(), stat = 'My Task Pending'
OUTPUT INSERTED.idx INTO @output
 WHERE stat IS NULL;


/* 내 작업 끌어오기 */
SELECT x.*
  FROM @output o INNER JOIN XTEST x ON x.idx = o.idx;

/* 뭔가 작업 */

/* 작업이 끝났음을 표시 */
UPDATE x
   SET end_dt = GETDATE(), stat = 'My Task Finished'
  FROM XTEST x INNER JOIN @output o ON o.idx = x.idx;

/* 데이터 확인 및 테스트 테이블 Drop */
SELECT * FROM XTEST;
DROP TABLE XTEST;

결과(캡처 이미지)

stat 값은 내가 생성한 유니크한 키를 집어 넣어 활용할 수 있기는 하지만, OUTPUT 을 활용함으로서, 어차피 내가 Update 한 값을 받아오기 때문에, 그냥 내 작업이 펜딩 되고 있다 정도만 표기하면 됩니다. 혹시라도 "뭔가 작업" 이 오래 걸리는 작업일 경우엔, 다른 모니터링 툴에서 펜딩되고 있는 작업을 알아보기 위해선, stat 값은 뭔가 일관된 값을 넣는 것이 좋겠죠.