OUTPUT 절(Transact-SQL)

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

, , 또는 문의 영향을 받는 각 행을 기반으로 하는 INSERTUPDATEDELETE정보 또는 MERGE 식을 반환합니다. 이러한 결과를 처리 애플리케이션에 반환하여 확인 메시지, 보관 및 기타 애플리케이션 요구 사항을 충족시키는 데 사용할 수 있습니다. 결과를 테이블 또는 테이블 변수에 삽입할 수도 있습니다. 또한 중첩DELETEINSERTUPDATE, 또는 MERGE 문에서 절의 OUTPUT 결과를 캡처하고 해당 결과를 대상 테이블 또는 뷰에 삽입할 수 있습니다.

참고 항목

UPDATEDELETE INSERT명령문에 오류가 발생하고 롤백되더라도 절이 있는 OUTPUT 문은 클라이언트에 행을 반환합니다. 문을 실행할 때 오류가 발생하는 경우 결과를 사용하면 안 됩니다.

사용 대상:

• DELETE
• INSERT
• UPDATE
• MERGE

Transact-SQL 구문 표기 규칙

구문

<OUTPUT_CLAUSE> ::=
{
    [ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ ( column_list ) ] ]
    [ OUTPUT <dml_select_list> ]
}
<dml_select_list> ::=
{ <column_name> | scalar_expression } [ [ AS ] column_alias_identifier ]
    [ , ...n ]

<column_name> ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }
    | $action

인수

@table_variable

반환된 행이 호출자에게 반환되는 대신 삽입되는 table 변수를 지정합니다. @table_variable , UPDATE, DELETE또는 MERGE 문 앞에 INSERT선언해야 합니다.

column_list 지정하지 않으면 테이블 변수의 열 수가 결과 집합과 OUTPUT 같아야 합니다. 단, ID 및 계산 열은 건너뛰므로 예외입니다. column_list가 지정되면 생략된 모든 열에 Null 값을 허용하거나 기본값을 할당해야 합니다.

테이블 변수에 대한 자세한 내용은 테이블을 참조 하세요.

output_table

반환된 행을 호출자에 반환하는 대신 삽입할 테이블을 지정합니다. output_table 임시 테이블일 수 있습니다.

column_list 지정하지 않으면 테이블의 열 수가 결과 집합과 OUTPUT 같아야 합니다. 단, ID 및 계산 열은 건너뛰므로 예외입니다. column_list가 지정되면 생략된 모든 열에 Null 값을 허용하거나 기본값을 할당해야 합니다.

output_table 다음을 수행할 수 없습니다.

• 활성화된 트리거를 정의할 수 없습니다.
• 제약 조건의 양쪽에 참여합니다 FOREIGN KEY .
• CHECK 제약 조건 또는 사용 규칙이 있습니다.

column_list

절의 대상 테이블에 INTO 있는 열 이름의 선택적 목록입니다. INSERT 문에 허용되는 열 목록과 유사합니다.

scalar_expression

단일 값으로 계산되는 기호와 연산자의 조합입니다. 집계 함수는 scalar_expression 허용되지 않습니다.

수정 중인 테이블의 열에 대한 모든 참조는 또는 DELETED 접두사로 INSERTED 한정되어야 합니다.

column_alias_identifier

열 이름을 참조하는 데 사용되는 대체 이름입니다.

DELETED

업데이트 또는 삭제 작업으로 삭제된 값과 현재 작업으로 변경되지 않는 기존 값을 지정하는 열 접두사입니다. 접두사로 접두사로 DELETED 지정된 열은 , DELETE또는 MERGE 문이 완료되기 전에 UPDATE값을 반영합니다.

DELETED는 문의 절 INSERT 과 함께 OUTPUT 사용할 수 없습니다.

INSERTED

삽입 또는 업데이트 작업에서 추가한 값과 현재 작업으로 변경되지 않는 기존 값을 지정하는 열 접두사입니다. 접두사로 접두사로 INSERTED 지정된 열은 , INSERT또는 MERGE 문이 완료된 후 UPDATE트리거가 실행되기 전에 값을 반영합니다.

INSERTED는 문의 절 DELETE 과 함께 OUTPUT 사용할 수 없습니다.

from_table_name

업데이트 또는 삭제할 행을 지정하는 데 사용되는 , UPDATE또는 MERGE 문의 절DELETE에 포함된 FROM 테이블을 지정하는 열 접두사입니다.

수정 중인 테이블이 절에도 FROM 지정된 경우 해당 테이블의 열에 대한 참조는 또는 DELETED 접두사로 INSERTED 한정되어야 합니다.

*

별표(*)는 삭제, 삽입 또는 업데이트 작업의 영향을 받는 모든 열이 테이블에 있는 순서대로 반환되도록 지정합니다.

예를 들어 OUTPUT DELETED.* 다음 DELETE 문에서는 테이블에서 삭제된 ShoppingCartItem 모든 열을 반환합니다.

DELETE Sales.ShoppingCartItem
    OUTPUT DELETED.*;

column_name

명시적 열 참조입니다. 수정되는 테이블에 대한 모든 참조는 다음과 INSERTED.<column_name>같이 적절하게 또는 DELETED 접두사로 INSERTED 올바르게 정규화되어야 합니다.

$action

문에 MERGE 만 사용할 수 있습니다. 해당 행에서 OUTPUT 수행된 작업에 따라 각 행DELETEINSERTUPDATE에 대한 세 가지 값 중 하나를 반환하는 문에서 MERGE nvarchar(10) 형식의 열을 지정합니다.

설명

절과 절은 OUTPUT <dml_select_list> OUTPUT <dml_select_list> INTO { @table_variable | output_table } 단일 INSERT문 UPDATEDELETE또는 MERGE 문으로 정의할 수 있습니다.

참고 항목

달리 지정하지 않는 한 절에 대한 참조는 OUTPUT 절과 절을 모두 OUTPUT 참조합니다 OUTPUT INTO .

이 절은 OUTPUT 작업 후 INSERT UPDATE ID 또는 계산 열의 값을 검색하는 데 유용할 수 있습니다.

계산 열이 포함된 <dml_select_list>경우 출력 테이블 또는 테이블 변수의 해당 열은 계산 열이 아닙니다. 새 열의 값은 문이 실행된 시점에 계산된 값입니다.

변경 내용이 테이블에 적용되는 순서와 행이 출력 테이블 또는 테이블 변수에 삽입되는 순서는 해당되지 않습니다.

매개 변수 또는 변수가 문의 OUTPUT 일부로 UPDATE 수정된 경우 절은 수정된 값 대신 문이 실행되기 전과 마찬가지로 항상 매개 변수 또는 변수의 값을 반환합니다.

구문을 사용하는 WHERE CURRENT OF 커서에 배치된 문 또는 DELETE 문과 함께 UPDATE 사용할 OUTPUT 수 있습니다.

절은 OUTPUT 다음 문에서 지원되지 않습니다.

• 분할된 로컬 뷰, 배포된 분할된 뷰 또는 원격 테이블을 참조하는 DML 문

• INSERT 문을 포함하는 문입니다 EXECUTE .

• 데이터베이스 호환성 수준이 100으로 설정된 경우 절에는 OUTPUT 전체 텍스트 조건자가 허용되지 않습니다.

• 이 절은 OUTPUT INTO 뷰 또는 행 집합 함수에 삽입하는 데 사용할 수 없습니다.

• 테이블이 대상인 절이 포함된 OUTPUT INTO 경우 사용자 정의 함수를 만들 수 없습니다.

비결정적 동작을 방지하기 위해 절에는 OUTPUT 다음 참조가 포함될 수 없습니다.

• 사용자 또는 시스템 데이터 액세스를 수행하거나 이러한 액세스를 수행하는 것으로 간주되는 하위 쿼리 또는 사용자 정의 함수입니다. 사용자 정의 함수는 스키마에 바인딩되지 않은 경우 데이터 액세스를 수행하는 것으로 간주됩니다.

• 해당 열이 다음 중 한 가지 방법으로 정의된 경우 뷰 또는 인라인 테이블 반환 함수의 열입니다.

  • 하위 쿼리

  • 사용자 또는 시스템 데이터 액세스를 수행하거나 이러한 액세스를 수행하는 것으로 간주되는 사용자 정의 함수

  • 해당 정의에서 사용자 또는 시스템 데이터 액세스를 수행하는 사용자 정의 함수가 포함된 계산 열

  SQL Server가 절에서 OUTPUT 이러한 열을 검색하면 오류 4186이 발생합니다.

OUTPUT 절에서 반환된 데이터를 테이블에 삽입합니다.

중첩INSERT, DELETEUPDATE또는 MERGE 문에서 절의 OUTPUT 결과를 캡처하고 해당 결과를 대상 테이블에 삽입하는 경우 다음 정보를 염두에 두어야 합니다.

• 전체 작업은 원자성을 갖습니다. 절이 INSERT 포함된 OUTPUT 문과 중첩된 DML 문이 모두 실행되거나 전체 문이 실패합니다.

• 외부 INSERT 문의 대상에는 다음과 같은 제한 사항이 적용됩니다.

  • 대상은 원격 테이블, 뷰 또는 공통 테이블 식일 수 없습니다.

  • 대상에는 제약 조건이 있거나 제약 조건에 의해 FOREIGN KEY 참조될 수 없습니다FOREIGN KEY.

  • 트리거는 대상에 정의할 수 없습니다.

  • 대상은 트랜잭션 복제에 대한 병합 복제 또는 업다이블 구독에 참여할 수 없습니다.

• 다음과 같은 제한 사항이 중첩된 DML 문에 적용됩니다.

  • 대상은 원격 테이블 또는 분할된 뷰일 수 없습니다.

  • 원본 자체에 절을 <dml_table_source> 포함할 수 없습니다.

• 절이 OUTPUT INTO 포함된 <dml_table_source> 문에서는 INSERT 이 절이 지원되지 않습니다.

• @@ROWCOUNT 는 외부 INSERT 문에 의해서만 삽입된 행을 반환합니다.

• @@IDENTITY- SCOPE_IDENTITYIDENT_CURRENT 중첩된 DML 문에 의해서만 생성되고 외부 INSERT 문에 의해 생성된 값이 아닌 ID 값을 반환합니다.

• 쿼리 알림은 문을 단일 엔터티로 처리하며, 외부 문 자체에서 INSERT 중요한 변경이 있더라도 생성된 메시지의 형식은 중첩된 DML의 형식입니다.

• <dml_table_source> 절 SELECT 에서 및 WHERE 절에는 하위 쿼리, 집계 함수, 순위 함수, 전체 텍스트 조건자, 데이터 액세스를 수행하는 사용자 정의 함수 또는 TEXTPTR() 함수를 포함할 수 없습니다.

병렬 처리

OUTPUT 클라이언트 또는 테이블 변수에 결과를 반환하는 절은 항상 직렬 계획을 사용합니다.

호환성 수준 130 이상으로 설정된 데이터베이스의 컨텍스트에서 작업에서 문에 대한 SELECT 힌트를 사용하고 WITH (TABLOCK) 임시 또는 사용자 테이블에 삽입하는 데 사용하는 OUTPUT...INTO 경우 INSERT...SELECT 하위 트리 비용에 따라 대상 테이블 INSERT...SELECT 이 병렬 처리에 적합합니다. 절에서 OUTPUT INTO 참조되는 대상 테이블은 병렬 처리에 적합하지 않습니다.

트리거

반환된 OUTPUT 열은 트리거가 실행되기 전 또는 명령문이 완료된 후 DELETE INSERTUPDATE의 데이터를 반영합니다.

트리거의 경우 INSTEAD OF 반환된 결과는 트리거 작업의 결과로 수정이 수행되지 않더라도 실제로 발생한 것처럼 INSERTUPDATEDELETE 생성됩니다. 절을 포함하는 OUTPUT 문이 트리거 본문 내에서 사용되는 경우 테이블 별칭을 사용하여 연결된 테이블과 DELETED 열 참조가 중복되지 않도록 삽입 및 삭제된 OUTPUT트리거 테이블을 참조 INSERTED 해야 합니다.

OUTPUT 키워드를 지정하지 않고 절을 지정하는 INTO 경우 DML 작업의 대상에는 지정된 DML 작업에 대해 정의된 활성화된 트리거가 있을 수 없습니다. 예를 들어 절이 OUTPUT 문에 UPDATE 정의된 경우 대상 테이블에 활성화된 트리거가 UPDATE 있을 수 없습니다.

sp_configure 트리거의 결과를 허용하지 않는 옵션이 설정 OUTPUT 되면 절이 없는 INTO 절은 트리거 내에서 호출될 때 문이 실패합니다.

데이터 형식

이 절은 OUTPUT nvarchar(max), varchar(max), varbinary(max), text, ntext, image 및 xml과 같은 큰 개체 데이터 형식을 지원합니다. 문의 절을 .WRITE UPDATE 사용하여 nvarchar(max), varchar(max) 또는 varbinary(max) 열을 수정하면 값의 전체 전후 이미지가 참조되는 경우 반환됩니다. 함수는 TEXTPTR() 절의 텍스트, ntext 또는 이미지 열에 대한 식의 OUTPUT 일부로 표시할 수 없습니다.

큐

테이블을 큐로 사용하는 애플리케이션에서 사용 OUTPUT 하거나 중간 결과 집합을 저장할 수 있습니다. 이 경우 애플리케이션은 지속적으로 테이블에 행을 추가하거나 제거합니다. 다음 예제에서는 문에서 절을 DELETE 사용하여 OUTPUT 삭제된 행을 호출 애플리케이션에 반환합니다.

USE AdventureWorks2022;
GO

DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT DELETED.*
WHERE DatabaseLogID = 7;
GO

이 예에서는 큐로 사용되는 테이블에서 행을 삭제하고 삭제된 값을 처리하는 애플리케이션에 반환하는 과정을 한 번의 작동으로 수행합니다. 테이블을 사용하여 스택을 구현하는 것과 같은 다른 의미 체계도 구현될 수 있습니다. 그러나 SQL Server는 절을 사용하여 OUTPUT DML 문에서 행을 처리하고 반환하는 순서를 보장하지 않습니다. 애플리케이션은 원하는 의미 체계를 보장할 수 있는 적절한 WHERE 절을 포함하거나 여러 행이 DML 작업에 적합할 수 있는 경우 보장된 순서가 없음을 이해해야 합니다. 다음 예에서는 하위 쿼리를 사용하며 필요한 정렬 구현을 위해 각 DatabaseLogID 열이 고유한 특성을 가짐을 가정합니다.

USE tempdb;
GO

CREATE TABLE dbo.table1
(
    id INT,
    employee VARCHAR(32)
);
GO

INSERT INTO dbo.table1
VALUES (1, 'Fred'),
    (2, 'Tom'),
    (3, 'Sally'),
    (4, 'Alice');
GO

DECLARE @MyTableVar TABLE (
    id INT,
    employee VARCHAR(32)
);

PRINT 'table1, before delete';

SELECT *
FROM dbo.table1;

DELETE
FROM dbo.table1
OUTPUT DELETED.*
INTO @MyTableVar
WHERE id = 4
    OR id = 2;

PRINT 'table1, after delete';

SELECT *
FROM dbo.table1;

PRINT '@MyTableVar, after delete';

SELECT *
FROM @MyTableVar;

DROP TABLE dbo.table1;

결과는 다음과 같습니다.

table1, before delete
id          employee
----------- ------------------------------
1           Fred
2           Tom
3           Sally
4           Alice

table1, after delete
id          employee
----------- ------------------------------
1           Fred
3           Sally

@MyTableVar, after delete
id          employee
----------- ------------------------------
2           Tom
4           Alice

참고 항목

시나리오에서 여러 애플리케이션이 READPAST UPDATE 한 테이블에서 파괴적인 읽기를 수행할 수 있도록 허용하는 경우 테이블 힌트 및 DELETE 문을 사용합니다. 이렇게 하면 다른 애플리케이션이 이미 테이블의 첫 번째 정규화 레코드를 읽고 있는 경우 발생할 수 있는 잠금 문제를 방지합니다.

사용 권한

SELECT 를 통해 <dml_select_list> 검색되거나 사용되는 <scalar_expression>모든 열에 대한 사용 권한이 필요합니다.

INSERT 에 지정된 <output_table>테이블에는 사용 권한이 필요합니다.

예제

이 문서의 Transact-SQL 코드 샘플은 AdventureWorks2022 또는 AdventureWorksDW2022 샘플 데이터베이스를 사용하며, 이는 Microsoft SQL Server 예시 및 커뮤니티 프로젝트(Microsoft SQL Server Samples and Community Projects) 홈 페이지에서 다운로드할 수 있습니다.

A. INSERT 문과 함께 OUTPUT INTO 사용

다음 예에서는 ScrapReason 테이블에 행을 삽입하고 OUTPUT 절을 사용하여 문의 결과를 @MyTableVar 테이블 변수에 반환합니다. 열이 ScrapReasonID IDENTITY 속성으로 정의되므로 해당 열에 대한 문에 값이 INSERT 지정되지 않습니다. 그러나 해당 열에 대한 데이터베이스 엔진 생성된 값은 열의 OUTPUT 절에 INSERTED.ScrapReasonID반환됩니다.

USE AdventureWorks2022;
GO

DECLARE @MyTableVar TABLE (
    NewScrapReasonID SMALLINT,
    Name VARCHAR(50),
    ModifiedDate DATETIME
);

INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason;
GO

B. DELETE 문과 함께 OUTPUT 사용

다음 예에서는 ShoppingCartItem 테이블의 모든 행을 삭제합니다. 이 절 OUTPUT DELETED.* 은 삭제된 행의 DELETE 모든 열인 문의 결과가 호출 애플리케이션에 반환되도록 지정합니다. 이어지는 SELECT 문은 ShoppingCartItem 테이블의 삭제 작업 결과를 확인합니다.

USE AdventureWorks2022;
GO

DELETE Sales.ShoppingCartItem
OUTPUT DELETED.*
WHERE ShoppingCartID = 20621;

--Verify the rows in the table matching the WHERE clause have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem WHERE ShoppingCartID = 20621;
GO

C. UPDATE 문과 함께 OUTPUT INTO 사용

다음 예에서는 VacationHours 테이블에 있는 처음 10개 행의 Employee 열을 25% 업데이트합니다. OUTPUT 절은 DELETED.VacationHours 열에서 UPDATE 문을 적용하기 전에 존재했던 VacationHours의 값과 INSERTED.VacationHours 열에서 업데이트된 값을 @MyTableVar 테이블 변수에 반환합니다.

다음 두 SELECT 문은 테이블의 업데이트 작업 Employee 결과와 값을 @MyTableVar 반환합니다.

USE AdventureWorks2022;
GO

DECLARE @MyTableVar TABLE (
    EmpID INT NOT NULL,
    OldVacationHours INT,
    NewVacationHours INT,
    ModifiedDate DATETIME);

UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE()
OUTPUT INSERTED.BusinessEntityID,
       DELETED.VacationHours,
       INSERTED.VacationHours,
       INSERTED.ModifiedDate
INTO @MyTableVar;

--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

D. OUTPUT INTO를 사용하여 식 반환

예 3을 기반으로 만들어진 다음 예에서는 업데이트된 OUTPUT 값과 업데이트가 적용되기 이전의 VacationHours 값 간의 차이를 나타내는 식을 VacationHours 절에 정의합니다. 이 식의 값은 VacationHoursDifference 열의 @MyTableVar 테이블 변수에 반환됩니다.

USE AdventureWorks2022;
GO

DECLARE @MyTableVar TABLE (
    EmpID INT NOT NULL,
    OldVacationHours INT,
    NewVacationHours INT,
    VacationHoursDifference INT,
    ModifiedDate DATETIME);

UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE()
OUTPUT INSERTED.BusinessEntityID,
       DELETED.VacationHours,
       INSERTED.VacationHours,
       INSERTED.VacationHours - DELETED.VacationHours,
       INSERTED.ModifiedDate
INTO @MyTableVar;

--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours,
    VacationHoursDifference, ModifiedDate
FROM @MyTableVar;
GO
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

E. UPDATE 문에서 from_table_name OUTPUT INTO 사용

다음 예제에서는 지정된 ProductID 및 ScrapReasonID가 있는 모든 작업 순서에 대해 WorkOrder 테이블의 ScrapReasonID 열을 업데이트합니다. OUTPUT INTO 절은 업데이트되는 테이블인 WorkOrder의 값과 더불어 Product 테이블의 값을 반환합니다. 업데이트할 행을 지정하기 위해 Product 테이블이 FROM 절에 사용됩니다. WorkOrder 테이블에는 AFTER UPDATE 트리거가 정의되어 있으므로 INTO 키워드가 필요합니다.

USE AdventureWorks2022;
GO

DECLARE @MyTestVar TABLE (
    OldScrapReasonID INT NOT NULL,
    NewScrapReasonID INT NOT NULL,
    WorkOrderID INT NOT NULL,
    ProductID INT NOT NULL,
    ProductName NVARCHAR(50)NOT NULL);

UPDATE Production.WorkOrder
SET ScrapReasonID = 4
OUTPUT DELETED.ScrapReasonID,
       INSERTED.ScrapReasonID,
       INSERTED.WorkOrderID,
       INSERTED.ProductID,
       p.Name
    INTO @MyTestVar
FROM Production.WorkOrder AS wo
    INNER JOIN Production.Product AS p
    ON wo.ProductID = p.ProductID
    AND wo.ScrapReasonID= 16
    AND p.ProductID = 733;

SELECT OldScrapReasonID, NewScrapReasonID, WorkOrderID,
    ProductID, ProductName
FROM @MyTestVar;
GO

F. DELETE 문에서 from_table_name OUTPUT INTO 사용

다음 예에서는 ProductProductPhoto 문의 FROM 절에 정의된 검색 조건에 따라 DELETE 테이블의 행을 삭제합니다. OUTPUT 절은 삭제되는 테이블인 DELETED.ProductID 및 DELETED.ProductPhotoID의 열과 더불어 Product 테이블의 열을 반환합니다. 이 테이블은 FROM 절에서 삭제할 행을 지정하기 위해 사용됩니다.

USE AdventureWorks2022;
GO

DECLARE @MyTableVar TABLE (
    ProductID INT NOT NULL,
    ProductName NVARCHAR(50)NOT NULL,
    ProductModelID INT NOT NULL,
    PhotoID INT NOT NULL);

DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
       p.Name,
       p.ProductModelID,
       DELETED.ProductPhotoID
    INTO @MyTableVar
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p
    ON ph.ProductID = p.ProductID
    WHERE p.ProductModelID BETWEEN 120 and 130;

--Display the results of the table variable.
SELECT ProductID, ProductName, ProductModelID, PhotoID
FROM @MyTableVar
ORDER BY ProductModelID;
GO

G. 큰 개체 데이터 형식으로 OUTPUT INTO 사용

다음 예제에서는 절을 사용하여 .WRITE 테이블의 nvarchar(max) 열에 있는 Production.Document 부분 값을 DocumentSummary업데이트합니다. 대체 단어, 기존 데이터에서 대체할 단어의 시작 위치(오프셋), 그리고 대체할 문자 수(길이)를 지정함으로써 components가 features로 대체됩니다. 이 예제에서는 OUTPUT 절을 사용해 DocumentSummary 열의 이전 및 이후 이미지를 @MyTableVar 테이블 변수에 반환합니다. 열의 DocumentSummary 전체 전후 이미지가 반환됩니다.

USE AdventureWorks2022;
GO

DECLARE @MyTableVar TABLE (
    SummaryBefore NVARCHAR(MAX),
    SummaryAfter NVARCHAR(MAX)
);

UPDATE Production.Document
SET DocumentSummary.WRITE(N'features', 28, 10)
OUTPUT DELETED.DocumentSummary,
       INSERTED.DocumentSummary
    INTO @MyTableVar
WHERE Title = N'Front Reflector Bracket Installation';

SELECT SummaryBefore, SummaryAfter
FROM @MyTableVar;
GO

H. INSTEAD OF 트리거에서 OUTPUT 사용

다음 예에서는 트리거에 OUTPUT 절을 사용하여 트리거 작업 결과를 반환합니다. 먼저 ScrapReason 테이블에서 뷰를 만들고 해당 뷰에서 사용자가 기본 테이블의 INSTEAD OF INSERT 열만 수정할 수 있게 하는 Name 트리거를 정의합니다. ScrapReasonID 열은 기본 테이블의 IDENTITY 열이기 때문에 트리거는 사용자가 제공한 값을 무시합니다. 대신 데이터베이스 엔진이 자동으로 올바른 값을 생성합니다. 또한 사용자가 제공한 ModifiedDate 값 역시 무시되고 현재 날짜로 설정됩니다. OUTPUT 절은 ScrapReason 테이블에 실제로 삽입된 값을 반환합니다.

USE AdventureWorks2022;
GO

IF OBJECT_ID('dbo.vw_ScrapReason', 'V') IS NOT NULL
    DROP VIEW dbo.vw_ScrapReason;
GO

CREATE VIEW dbo.vw_ScrapReason
AS
SELECT ScrapReasonID,
    Name,
    ModifiedDate
FROM Production.ScrapReason;
GO

CREATE TRIGGER dbo.io_ScrapReason ON dbo.vw_ScrapReason
INSTEAD OF INSERT
AS
BEGIN
    --ScrapReasonID is not specified in the list of columns to be inserted
    --because it is an IDENTITY column.
    INSERT INTO Production.ScrapReason (
        Name,
        ModifiedDate
    )
    OUTPUT INSERTED.ScrapReasonID,
        INSERTED.Name,
        INSERTED.ModifiedDate
    SELECT Name, GETDATE()
    FROM INSERTED;
END
GO

INSERT vw_ScrapReason (
    ScrapReasonID,
    Name,
    ModifiedDate
)
VALUES (
    99,
    N'My scrap reason',
    '20030404'
);
GO

다음은 2004년 4월 12일('2004-04-12')에 생성된 결과 집합입니다. 및 ModifiedDate 열은 ScrapReasonIDActual 문에 제공된 값 대신 트리거 작업에서 INSERT 생성된 값을 반영합니다.

ScrapReasonID  Name             ModifiedDate
-------------  ---------------- -----------------------
17             My scrap reason  2004-04-12 16:23:33.050

9\. ID 및 계산 열에 OUTPUT INTO 사용

다음 예에서는 EmployeeSales 테이블을 만들고 INSERT 문에 SELECT 문을 사용하여 이 테이블에 여러 개의 행을 삽입한 후 원본 테이블에서 데이터를 검색합니다. EmployeeSales 테이블에는 ID 열(EmployeeID)과 계산 열(ProjectedSales)이 포함되어 있습니다.

USE AdventureWorks2022;
GO

IF OBJECT_ID('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO

CREATE TABLE dbo.EmployeeSales (
    EmployeeID INT IDENTITY(1, 5) NOT NULL,
    LastName NVARCHAR(20) NOT NULL,
    FirstName NVARCHAR(20) NOT NULL,
    CurrentSales MONEY NOT NULL,
    ProjectedSales AS CurrentSales * 1.10
);
GO

DECLARE @MyTableVar TABLE (
    EmployeeID INT NOT NULL,
    LastName NVARCHAR(20) NOT NULL,
    FirstName NVARCHAR(20) NOT NULL,
    CurrentSales MONEY NOT NULL,
    ProjectedSales MONEY NOT NULL
);

INSERT INTO dbo.EmployeeSales (
    LastName,
    FirstName,
    CurrentSales
)
OUTPUT INSERTED.EmployeeID,
    INSERTED.LastName,
    INSERTED.FirstName,
    INSERTED.CurrentSales,
    INSERTED.ProjectedSales
INTO @MyTableVar
SELECT c.LastName,
    c.FirstName,
    sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
    ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.BusinessEntityID LIKE '2%'
ORDER BY c.LastName,
    c.FirstName;

SELECT EmployeeID,
    LastName,
    FirstName,
    CurrentSales,
    ProjectedSales
FROM @MyTableVar;
GO

SELECT EmployeeID,
    LastName,
    FirstName,
    CurrentSales,
    ProjectedSales
FROM dbo.EmployeeSales;
GO

J. 단일 문에서 OUTPUT 및 OUTPUT INTO 사용

다음 예에서는 ProductProductPhoto 문의 FROM 절에 정의된 검색 조건에 따라 DELETE 테이블의 행을 삭제합니다. OUTPUT INTO 절은 삭제되는 테이블(DELETED.ProductID 및 DELETED.ProductPhotoID)의 열과 Product 테이블의 열을 @MyTableVar 테이블 변수에 반환합니다. Product 테이블은 FROM 절에서 삭제할 행을 지정하기 위해 사용됩니다. 이 절은 OUTPUT 테이블에서 호출 애플리케이션으로 행이 삭제된 ProductProductPhoto 날짜 및 시간을 반환 DELETED.ProductIDDELETED.ProductPhotoID 합니다.

USE AdventureWorks2022;
GO

DECLARE @MyTableVar TABLE (
    ProductID INT NOT NULL,
    ProductName NVARCHAR(50) NOT NULL,
    ProductModelID INT NOT NULL,
    PhotoID INT NOT NULL
);

DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
    p.Name,
    p.ProductModelID,
    DELETED.ProductPhotoID
INTO @MyTableVar
OUTPUT DELETED.ProductID,
    DELETED.ProductPhotoID,
    GETDATE() AS DeletedDate
FROM Production.ProductProductPhoto AS ph
INNER JOIN Production.Product AS p
    ON ph.ProductID = p.ProductID
WHERE p.ProductID BETWEEN 800
        AND 810;

--Display the results of the table variable.
SELECT ProductID,
    ProductName,
    PhotoID,
    ProductModelID
FROM @MyTableVar;
GO

K. OUTPUT 절에서 반환된 데이터 삽입

다음 예에서는 OUTPUT 문의 MERGE 절에서 반환되는 데이터를 캡처하고 이 데이터를 다른 테이블에 삽입합니다. MERGE 문은 Quantity 테이블에서 처리하는 순서대로 ProductInventory 테이블의 SalesOrderDetail 열을 매일 업데이트합니다. 또한 재고가 감소 0 하는 제품의 행도 삭제합니다. 이 예에서는 삭제된 행을 캡처한 후 다른 ZeroInventory 테이블에 삽입하여 재고가 없는 제품을 추적합니다.

USE AdventureWorks2022;
GO

IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL
    DROP TABLE Production.ZeroInventory;
GO

--Create ZeroInventory table.
CREATE TABLE Production.ZeroInventory (
    DeletedProductID INT,
    RemovedOnDate DATETIME
    );
GO

INSERT INTO Production.ZeroInventory (
    DeletedProductID,
    RemovedOnDate
)
SELECT ProductID,
    GETDATE()
FROM (
    MERGE Production.ProductInventory AS pi
    USING (
        SELECT ProductID,
            SUM(OrderQty)
        FROM Sales.SalesOrderDetail AS sod
        INNER JOIN Sales.SalesOrderHeader AS soh
            ON sod.SalesOrderID = soh.SalesOrderID
                AND soh.OrderDate = '20070401'
        GROUP BY ProductID
        ) AS src(ProductID, OrderQty)
        ON (pi.ProductID = src.ProductID)
    WHEN MATCHED
        AND pi.Quantity - src.OrderQty <= 0
        THEN
            DELETE
    WHEN MATCHED
        THEN
            UPDATE
            SET pi.Quantity = pi.Quantity - src.OrderQty
    OUTPUT $ACTION,
        DELETED.ProductID
    ) AS Changes(Action, ProductID)
WHERE Action = 'DELETE';

IF @@ROWCOUNT = 0
    PRINT 'Warning: No rows were inserted';
GO

SELECT DeletedProductID,
    RemovedOnDate
FROM Production.ZeroInventory;
GO

관련 콘텐츠

• DELETE (Transact-SQL)
• INSERT(Transact-SQL)
• UPDATE(Transact-SQL)
• table(Transact-SQL)
• CREATE TRIGGER(Transact-SQL)
• sp_configure(Transact-SQL)