• 아티클

INSERT 예(Transact-SQL)

이 항목에서는 SQL Server 2008 R2에서 Transact-SQL INSERT 문을 사용하는 예를 제공합니다. INSERT 예는 다음 범주별로 분류되어 있습니다.

범주

중요한 구문 요소

기본 구문

INSERT • 테이블 값 생성자

열 값 처리

IDENTITY • NEWID • 기본값 • 사용자 정의 형식

다른 테이블의 데이터 삽입

INSERT…SELECT • INSERT…EXECUTE • WITH 공통 테이블 식 • TOP

표준 테이블 이외의 대상 개체 지정

뷰 • 테이블 변수

원격 테이블에 행 삽입

연결된 서버 • OPENQUERY 행 집합 함수 • OPENDATASOURCE 행 집합 함수

테이블 또는 데이터 파일에서 데이터 대량 가져오기

INSERT…SELECT • OPENROWSET 함수

힌트를 사용하여 쿼리 최적화 프로그램의 기본 동작 재정의

테이블 힌트

INSERT 문의 결과 캡처

OUTPUT 절

기본 구문

이 섹션의 예에서는 최소 필수 구문을 사용하여 INSERT 문의 기본 기능을 보여 줍니다.

1. 단일 데이터 행 삽입

다음 예에서는 Production.UnitMeasure 테이블에 한 행을 삽입합니다. 이 테이블의 열은 UnitMeasureCode, Name 및 ModifiedDate입니다. 모든 열의 값이 제공되고 테이블의 열과 같은 순서로 나열되어 있으므로 열 목록에 열 이름을 지정할 필요가 없습니다.

USE AdventureWorks2008R2;
GO
INSERT INTO Production.UnitMeasure
VALUES (N'FT', N'Feet', '20080414');
GO

2. 여러 데이터 행 삽입

다음 예에서는 테이블 값 생성자를 사용하여 단일 INSERT 문으로 Production.UnitMeasure 테이블에 세 개의 행을 삽입합니다. 모든 열의 값이 제공되고 테이블의 열과 같은 순서로 나열되어 있으므로 열 목록에 열 이름을 지정할 필요가 없습니다.

USE AdventureWorks2008R2;
GO
INSERT INTO Production.UnitMeasure
VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923'), (N'Y3', N'Cubic Yards', '20080923');
GO

3. 테이블 열과 순서가 다른 데이터 삽입

다음 예에서는 열 목록을 사용하여 각 열에 삽입되는 값을 명시적으로 지정합니다. Production.UnitMeasure 테이블의 열 순서는 UnitMeasureCode, Name, ModifiedDate이지만 column_list.에는 열이 이 순서대로 나열되어 있지 않습니다.

USE AdventureWorks2008R2;
GO
INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode,
    ModifiedDate)
VALUES (N'Square Yards', N'Y2', GETDATE());
GO

열 값 처리

이 섹션의 예에서는 IDENTITY 속성, DEFAULT 값, uniqueidentifer 데이터 형식으로 정의된 열 또는 사용자 정의 형식 열에 값을 삽입하는 방법을 각각 보여 줍니다.

1. 기본값을 갖는 열이 포함된 테이블에 데이터 삽입

다음 예에서는 값을 자동으로 생성하거나 기본값을 갖는 열이 포함된 테이블에 행을 삽입하는 방법을 보여 줍니다. Column_1은 column_2에 삽입된 값과 문자열을 연결하여 자동으로 값을 생성하는 계산 열입니다. Column_2는 기본 제약 조건으로 정의됩니다. 이 열에 값이 지정되지 않으면 기본값이 사용됩니다. Column_3은 고유한 증분 이진수를 생성하는 rowversion 데이터 형식으로 정의됩니다. Column_4는 값을 자동으로 생성하지 않습니다. 이 열의 값이 지정되지 않으면 NULL이 삽입됩니다. INSERT 문은 전체 열이 아니라 일부 열의 값을 포함하는 행을 삽입합니다. 마지막 INSERT 문에는 열이 지정되지 않고 DEFAULT VALUES 절을 사용하여 기본값만 삽입됩니다.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 
(
    column_1 AS 'Computed column ' + column_2, 
    column_2 varchar(30) 
        CONSTRAINT default_name DEFAULT ('my column default'),
    column_3 rowversion,
    column_4 varchar(40) NULL
);
GO
INSERT INTO dbo.T1 (column_4) 
    VALUES ('Explicit value');
INSERT INTO dbo.T1 (column_2, column_4) 
    VALUES ('Explicit value', 'Explicit value');
INSERT INTO dbo.T1 (column_2) 
    VALUES ('Explicit value');
INSERT INTO T1 DEFAULT VALUES; 
GO
SELECT column_1, column_2, column_3, column_4
FROM dbo.T1;
GO

2. ID 열이 있는 테이블에 데이터 삽입

다음 예에서는 ID 열에 데이터를 삽입하는 다양한 방법을 보여 줍니다. 처음 두 INSERT 문은 새 행에 대해 ID 값을 생성할 수 있도록 합니다. 세 번째 INSERT 문은 SET IDENTITY_INSERT 문으로 열의 IDENTITY 속성을 재정의하고 ID 열에 명시적 값을 삽입합니다.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 ( column_1 int IDENTITY, column_2 VARCHAR(30));
GO
INSERT T1 VALUES ('Row #1');
INSERT T1 (column_2) VALUES ('Row #2');
GO
SET IDENTITY_INSERT T1 ON;
GO
INSERT INTO T1 (column_1,column_2) 
    VALUES (-99, 'Explicit identity value');
GO
SELECT column_1, column_2
FROM T1;
GO

3. NEWID()를 사용하여 uniqueidentifier 열에 데이터 삽입

다음 예에서는 NEWID() 함수를 사용하여 column_2의 GUID를 가져옵니다. 두 번째 INSERT 문에서 볼 수 있듯이 ID 열과 달리 uniqueidentifier 데이터 형식의 열에 대해서는 데이터베이스 엔진에서 값을 자동으로 생성하지 않습니다.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 
(
    column_1 int IDENTITY, 
    column_2 uniqueidentifier,
);
GO
INSERT INTO dbo.T1 (column_2) 
    VALUES (NEWID());
INSERT INTO T1 DEFAULT VALUES; 
GO
SELECT column_1, column_2
FROM dbo.T1;
GO

4. 사용자 정의 형식 열에 데이터 삽입

다음 Transact-SQL 문은 Points 테이블의 PointValue 열에 세 개의 행을 삽입합니다. 이 열은 CLR UDT(사용자 정의 형식)를 사용합니다. Point 데이터 형식은 UDT 속성으로 노출되는 X 및 Y 정수 값으로 구성됩니다. CAST 또는 CONVERT 함수를 사용하여 쉼표로 구분된 X 및 Y 값을 Point 형식으로 캐스팅해야 합니다. 처음 두 문은 CONVERT 함수를 사용하여 문자열 값을 Point 형식으로 변환하고 세 번째 문은 CAST 함수를 사용합니다. 자세한 내용은 UDT 데이터 조작을 참조하십시오.

INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '3,4'));
INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '1,5'));
INSERT INTO dbo.Points (PointValue) VALUES (CAST ('1,99' AS Point));

다른 테이블의 데이터 삽입

이 섹션의 예에서는 한 테이블의 행을 다른 테이블에 삽입하는 방법을 보여 줍니다.

1. SELECT 및 EXECUTE 옵션을 사용하여 다른 테이블의 데이터 삽입

다음 예에서는 INSERT…SELECT 또는 INSERT…EXECUTE를 사용하여 한 테이블의 데이터를 다른 테이블에 삽입하는 방법을 보여 줍니다. 이 방법은 모두 열 목록에 리터럴 값과 식을 포함하는 다중 테이블 SELECT 문을 기반으로 수행됩니다.

첫 번째 INSERT 문은 SELECT 문을 사용하여 원본 테이블(Employee, SalesPerson 및 Person)에서 데이터를 파생시키고 결과 집합을 EmployeeSales 테이블에 저장합니다. 두 번째 INSERT 문은 EXECUTE 절을 사용하여 SELECT 문을 포함하는 저장 프로시저를 호출하고 세 번째 INSERT 문은 EXECUTE 절을 사용하여 SELECT 문을 리터럴 문자열로 참조합니다.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO
IF OBJECT_ID ('dbo.uspGetEmployeeSales', 'P') IS NOT NULL
    DROP PROCEDURE uspGetEmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( DataSource   varchar(20) NOT NULL,
  BusinessEntityID   varchar(11) NOT NULL,
  LastName     varchar(40) NOT NULL,
  SalesDollars money NOT NULL
);
GO
CREATE PROCEDURE dbo.uspGetEmployeeSales 
AS 
    SET NOCOUNT ON;
    SELECT 'PROCEDURE', sp.BusinessEntityID, c.LastName, 
        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 sp.BusinessEntityID, c.LastName;
GO
--INSERT...SELECT example
INSERT INTO dbo.EmployeeSales
    SELECT 'SELECT', sp.BusinessEntityID, c.LastName, 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 sp.BusinessEntityID, c.LastName;
GO
--INSERT...EXECUTE procedure example
INSERT INTO dbo.EmployeeSales 
EXECUTE dbo.uspGetEmployeeSales;
GO
--INSERT...EXECUTE('string') example
INSERT INTO dbo.EmployeeSales 
EXECUTE 
('
SELECT ''EXEC STRING'', sp.BusinessEntityID, c.LastName, 
    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 sp.BusinessEntityID, c.LastName
');
GO
--Show results.
SELECT DataSource,BusinessEntityID,LastName,SalesDollars
FROM dbo.EmployeeSales;
GO

2. WITH 공통 테이블 식을 사용하여 삽입할 데이터 정의

다음 예에서는 NewEmployee 테이블을 만듭니다. 공통 테이블 식(EmployeeTemp)은 하나 이상의 테이블에서 NewEmployee 테이블에 삽입할 행을 정의합니다. INSERT 문은 공통 테이블 식의 열을 참조합니다.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'HumanResources.NewEmployee', N'U') IS NOT NULL
    DROP TABLE HumanResources.NewEmployee;
GO
CREATE TABLE HumanResources.NewEmployee
(
    EmployeeID int NOT NULL,
    LastName nvarchar(50) NOT NULL,
    FirstName nvarchar(50) NOT NULL,
    PhoneNumber Phone NULL,
    AddressLine1 nvarchar(60) NOT NULL,
    City nvarchar(30) NOT NULL,
    State nchar(3) NOT NULL, 
    PostalCode nvarchar(15) NOT NULL,
    CurrentFlag Flag
);
GO
WITH EmployeeTemp (EmpID, LastName, FirstName, Phone, 
                   Address, City, StateProvince, 
                   PostalCode, CurrentFlag)
AS (SELECT 
       e.BusinessEntityID, c.LastName, c.FirstName, pp.PhoneNumber,
       a.AddressLine1, a.City, sp.StateProvinceCode, 
       a.PostalCode, e.CurrentFlag
    FROM HumanResources.Employee e
        INNER JOIN Person.BusinessEntityAddress AS bea
        ON e.BusinessEntityID = bea.BusinessEntityID
        INNER JOIN Person.Address AS a
        ON bea.AddressID = a.AddressID
        INNER JOIN Person.PersonPhone AS pp
        ON e.BusinessEntityID = pp.BusinessEntityID
        INNER JOIN Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
        INNER JOIN Person.Person as c
        ON e.BusinessEntityID = c.BusinessEntityID
    )
INSERT INTO HumanResources.NewEmployee 
    SELECT EmpID, LastName, FirstName, Phone, 
           Address, City, StateProvince, PostalCode, CurrentFlag
    FROM EmployeeTemp;
GO

3. TOP을 사용하여 원본 테이블에서 삽입되는 데이터 제한

다음 예에서는 TOP 절을 사용하여 Employee 테이블에서 NewEmployee 테이블에 삽입되는 행 수를 제한합니다. 이 예에서는 Employee 테이블에서 무작위로 선별한 직원 10명의 주소 데이터를 여기에 삽입합니다. 그런 다음 SELECT 문을 실행하여 NewEmployee 테이블의 내용을 확인합니다.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'HumanResources.NewEmployee', N'U') IS NOT NULL
    DROP TABLE HumanResources.NewEmployee;
GO
CREATE TABLE HumanResources.NewEmployee
(
    BusinessEntityID int NOT NULL,
    LastName nvarchar(50) NOT NULL,
    FirstName nvarchar(50) NOT NULL,
    PhoneNumber Phone NULL,
    AddressLine1 nvarchar(60) NOT NULL,
    City nvarchar(30) NOT NULL,
    State nchar(3) NOT NULL, 
    PostalCode nvarchar(15) NOT NULL,
    CurrentFlag Flag
);
GO
-- Insert 10 random rows into the table NewEmployee.
INSERT TOP (10) INTO HumanResources.NewEmployee 
    SELECT
       e.BusinessEntityID, c.LastName, c.FirstName, pp.PhoneNumber,
       a.AddressLine1, a.City, sp.StateProvinceCode, 
       a.PostalCode, e.CurrentFlag
    FROM HumanResources.Employee e
        INNER JOIN Person.BusinessEntityAddress AS bea
        ON e.BusinessEntityID = bea.BusinessEntityID
        INNER JOIN Person.Address AS a
        ON bea.AddressID = a.AddressID
        INNER JOIN Person.PersonPhone AS pp
        ON e.BusinessEntityID = pp.BusinessEntityID
        INNER JOIN Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
        INNER JOIN Person.Person as c
        ON e.BusinessEntityID = c.BusinessEntityID;
GO
SELECT  BusinessEntityID, LastName, FirstName, PhoneNumber,
        AddressLine1, City, State, PostalCode, CurrentFlag
FROM HumanResources.NewEmployee;
GO

표준 테이블 이외의 대상 개체 지정

이 섹션의 예에서는 뷰 또는 테이블 변수를 지정하여 행을 삽입하는 방법을 보여 줍니다.

1. 뷰를 지정하여 데이터 삽입

다음 예에서는 뷰 이름을 대상 개체로 지정하지만 새 행이 기본 테이블에 삽입됩니다. INSERT 문의 값 순서는 뷰의 열 순서와 일치해야 합니다. 자세한 내용은 뷰를 통해 데이터 수정을 참조하십시오.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
IF OBJECT_ID ('dbo.V1', 'V') IS NOT NULL
    DROP VIEW dbo.V1;
GO
CREATE TABLE T1 ( column_1 int, column_2 varchar(30));
GO
CREATE VIEW V1 AS 
SELECT column_2, column_1 
FROM T1;
GO
INSERT INTO V1 
    VALUES ('Row 1',1);
GO
SELECT column_1, column_2 
FROM T1;
GO
SELECT column_1, column_2
FROM V1;
GO

2. 테이블 변수에 데이터 삽입

다음 예에서는 테이블 변수를 대상 개체로 지정합니다.

USE AdventureWorks2008R2;
GO
-- Create the table variable.
DECLARE @MyTableVar table(
    LocationID int NOT NULL,
    CostRate smallmoney NOT NULL,
    NewCostRate AS CostRate * 1.5,
    ModifiedDate datetime);

-- Insert values into the table variable.
INSERT INTO @MyTableVar (LocationID, CostRate, ModifiedDate)
    SELECT LocationID, CostRate, GETDATE() FROM Production.Location
    WHERE CostRate > 0;

-- View the table variable result set.
SELECT * FROM @MyTableVar;
GO

원격 테이블에 행 삽입

이 섹션의 예에서는 원격 테이블을 참조하는 행 집합 함수 또는 연결된 서버를 사용하여 원격 대상 테이블에 행을 삽입하는 방법을 보여 줍니다.

1. 연결된 서버를 사용하여 원격 테이블에 데이터 삽입

다음 예에서는 원격 테이블에 행을 삽입합니다. 먼저 sp_addlinkedserver를 사용하여 원격 데이터 원본에 대한 링크를 만듭니다. 그런 다음 연결된 서버 이름 MyLinkServer가 server.catalog.schema.object와 같이 네 부분으로 구성된 개체 이름의 일부로 지정됩니다.

USE master;
GO
-- Create a link to the remote data source. 
-- Specify a valid server name for @datasrc as 'server_name' or 'server_name\instance_name'.

EXEC sp_addlinkedserver @server = N'MyLinkServer',
    @srvproduct = N' ',
    @provider = N'SQLNCLI', 
    @datasrc = N'server_name',
    @catalog = N'AdventureWorks2008R2';
GO

USE AdventureWorks2008R2;
GO
-- Specify the remote data source in the FROM clause using a four-part name 
-- in the form linked_server.catalog.schema.object.

INSERT INTO MyLinkServer.AdventureWorks2008R2.HumanResources.Department (Name, GroupName)
VALUES (N'Public Relations', N'Executive General and Administration');
GO

2. OPENQUERY 함수를 사용하여 원격 테이블에 데이터 삽입

다음 예에서는 OPENQUERY 행 집합 함수를 지정하여 원격 테이블에 행을 삽입합니다. 이 예에서는 이전 예에서 만든 연결된 서버 이름이 사용됩니다.

-- Use the OPENQUERY function to access the remote data source.

INSERT OPENQUERY (MyLinkServer, 'SELECT Name, GroupName FROM AdventureWorks2008R2.HumanResources.Department')
VALUES ('Environmental Impact', 'Engineering');
GO

3. OPENDATASOURCE 함수를 사용하여 원격 테이블에 데이터 삽입

다음 예에서는 OPENDATASOURCE 행 집합 함수를 지정하여 원격 테이블에 행을 삽입합니다. server_name 또는 server_name\instance_name 형식을 사용하여 데이터 원본에 대해 유효한 서버 이름을 지정해야 합니다.

-- Use the OPENDATASOURCE function to specify the remote data source.
-- Specify a valid server name for Data Source using the format server_name or server_name\instance_name.

INSERT INTO OPENDATASOURCE('SQLNCLI',
    'Data Source= <server_name>; Integrated Security=SSPI')
    .AdventureWorks2008R2.HumanResources.Department (Name, GroupName)
    VALUES (N'Standards and Methods', 'Quality Assurance');
GO

테이블 또는 데이터 파일에서 데이터 대량 가져오기

이 섹션의 예에서는 INSERT 문을 사용하여 데이터를 테이블에 대량으로 가져오는(대량으로 로드하는) 두 가지 방법을 보여 줍니다.

1. 최소 로깅으로 힙에 데이터 삽입

다음 예에서는 새 테이블(힙)을 만들고 최소 로깅을 사용하여 다른 테이블의 데이터를 새 테이블에 삽입합니다. 여기에서는 AdventureWorks2008R2 데이터베이스의 복구 모델이 FULL로 설정되었다고 가정합니다. 최소 로깅을 사용할 수 있도록 AdventureWorks2008R2 데이터베이스의 복구 모델은 행 삽입 전에 BULK_LOGGED로 설정되고 INSERT INTO…SELECT 문 실행 후에는 FULL로 재설정됩니다. 또한 TABLOCK 힌트가 대상 테이블 Sales.SalesHistory에 대해 지정됩니다. 이렇게 하면 문은 트랜잭션 로그에 최소 공간을 사용하여 효율적으로 수행됩니다.

USE AdventureWorks2008R2;
GO
-- Create the target heap.
CREATE TABLE Sales.SalesHistory(
    SalesOrderID int NOT NULL,
    SalesOrderDetailID int NOT NULL,
    CarrierTrackingNumber nvarchar(25) NULL,
    OrderQty smallint NOT NULL,
    ProductID int NOT NULL,
    SpecialOfferID int NOT NULL,
    UnitPrice money NOT NULL,
    UnitPriceDiscount money NOT NULL,
    LineTotal money NOT NULL,
    rowguid uniqueidentifier ROWGUIDCOL  NOT NULL,
    ModifiedDate datetime NOT NULL );
GO
-- Temporarily set the recovery model to BULK_LOGGED.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY BULK_LOGGED;
GO
-- Transfer data from Sales.SalesOrderDetail to Sales.SalesHistory
INSERT INTO Sales.SalesHistory WITH (TABLOCK)
    (SalesOrderID, 
     SalesOrderDetailID,
     CarrierTrackingNumber, 
     OrderQty, 
     ProductID, 
     SpecialOfferID, 
     UnitPrice, 
     UnitPriceDiscount,
     LineTotal, 
     rowguid, 
     ModifiedDate)
SELECT * FROM Sales.SalesOrderDetail;
GO
-- Reset the recovery model.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO

2. OPENROWSET 함수를 BULK와 함께 사용하여 테이블에 데이터 대량 가져오기

다음 예에서는 OPENROWSET 함수를 지정하여 데이터 파일의 행을 테이블에 삽입합니다. 성능 최적화를 위해 IGNORE_TRIGGERS 테이블 힌트가 지정됩니다. 자세한 내용은 BULK INSERT 또는 OPENROWSET(BULK...)를 사용하여 데이터 대량 가져오기를 참조하십시오.

-- Use the OPENROWSET function to specify the data source and specifies the IGNORE_TRIGGERS table hint.
INSERT INTO HumanResources.Department WITH (IGNORE_TRIGGERS) (Name, GroupName)
SELECT b.Name, b.GroupName 
FROM OPENROWSET (
    BULK 'C:\SQLFiles\DepartmentData.txt',
    FORMATFILE = 'C:\SQLFiles\BulkloadFormatFile.xml',
    ROWS_PER_BATCH = 15000)AS b ;
GO

힌트를 사용하여 쿼리 최적화 프로그램의 기본 동작 재정의

이 섹션의 예에서는 INSERT 문을 처리할 때 쿼리 최적화 프로그램의 기본 동작을 임시로 재정의하기 위해 테이블 힌트를 사용하는 방법을 보여 줍니다.

주의 사항주의

일반적으로 SQL Server 쿼리 최적화 프로그램은 쿼리에 대해 최상의 실행 계획을 선택하므로 숙련된 개발자 및 데이터베이스 관리자가 마지막 방법으로만 힌트를 사용하는 것이 좋습니다.

1. TABLOCK 힌트를 사용하여 잠금 방법 지정

다음 예에서는 Production.Location 테이블에 배타적(X) 잠금을 적용하고 INSERT 문이 끝날 때까지 유지하도록 지정합니다.

USE AdventureWorks2008R2;
GO
INSERT INTO Production.Location WITH (XLOCK)
(Name, CostRate, Availability)
VALUES ( N'Final Inventory', 15.00, 80.00);
GO

INSERT 문의 결과 캡처

이 섹션의 예에서는 OUTPUT 절을 사용하여 INSERT 문의 영향을 받는 각 행의 정보 또는 각 행을 기반으로 하는 식을 반환하는 방법을 보여 줍니다. 이러한 결과를 처리 응용 프로그램에 반환하여 확인 메시지, 보관 및 기타 응용 프로그램 요구 사항을 충족시키는 데 사용할 수 있습니다.

1. INSERT 문에 OUTPUT 사용

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

USE AdventureWorks2008R2;
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

2. ID 열 및 계산 열에 OUTPUT 사용

다음 예에서는 EmployeeSales 테이블을 만든 다음, INSERT 문에 SELECT 문을 사용하여 이 테이블에 여러 개의 행을 삽입한 후 원본 테이블에서 데이터를 검색합니다. EmployeeSales 테이블에는 ID 열(EmployeeID)과 계산 열(ProjectedSales)이 포함되어 있습니다. 이러한 값은 삽입 작업 중에 데이터베이스 엔진에서 생성되므로 @MyTableVar에 이러한 열을 정의할 수 없습니다.

USE AdventureWorks2008R2 ;
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(
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  CurrentSales money NOT NULL
  );

INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)
  OUTPUT INSERTED.LastName, 
         INSERTED.FirstName, 
         INSERTED.CurrentSales
  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 LastName, FirstName, CurrentSales
FROM @MyTableVar;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GO

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

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

USE AdventureWorks2008R2;
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
           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;