테이블 값 생성자(Transact-SQL)

테이블에 일련의 행 값 식을 생성하도록 지정합니다. Transact-SQL 테이블 값 생성자를 사용하면 단일 DML 문에 여러 데이터 행을 지정할 수 있습니다. 테이블 값 생성자를 MERGE 문의 USING <source table>절에 있는 INSERT 문의 VALUES 절 및 FROM 절의 파생 테이블 정의에 지정할 수 있습니다.

Transact-SQL 구문 표기 규칙

구문

VALUES ( <row value expression list> ) [ ,...n ] 

<row value expression list> ::=
    {<row value expression> } [ ,...n ]

<row value expression> ::=
    { DEFAULT | NULL | expression }

인수

• VALUES
  행 값 식 목록을 표시합니다. 각 목록은 괄호로 묶고 쉼표로 구분해야 합니다.

  각 목록에 지정된 값의 수는 동일해야 하며 값은 테이블 열과 같은 순서로 되어 있어야 합니다. 테이블의 각 열에 대해 값이 지정되어야 하며 열 목록은 들어오는 값 각각에 대한 열을 명시적으로 지정해야 합니다.

• DEFAULT
  데이터베이스 엔진에서 열에 대해 정의된 기본값을 삽입하도록 설정합니다. 열에 대한 기본값이 없고 열에 Null 값을 사용할 수 있는 경우에는 NULL이 삽입됩니다. ID 열에는 DEFAULT를 사용할 수 없습니다. 테이블 값 생성자에 지정된 경우 DEFAULT는 INSERT 문에서만 허용됩니다.

• expression
  상수, 변수 또는 식입니다. 식은 EXECUTE 문을 포함할 수 없습니다.

제한 사항

테이블 값 생성자를 사용하여 생성할 수 있는 최대 행 수는 1000개입니다. 1000개 이상의 행을 삽입하려면 여러 INSERT 문을 만들거나 bcp 유틸리티 또는 BULK INSERT 문을 사용하여 데이터를 대량으로 가져옵니다.

단일 스칼라 값만 행 값 식으로 허용됩니다. 여러 열을 포함하는 하위 쿼리는 행 값 식으로 허용되지 않습니다. 예를 들어 다음 코드에서는 세 번째 행 값 식 목록에 여러 열을 포함하는 하위 쿼리가 있으므로 구문 오류가 발생합니다.

USE AdventureWorks;
GO
CREATE TABLE dbo.MyProducts (Name varchar(50), ListPrice money);
GO
-- This statement fails because the third values list contains multiple columns in the subquery.
INSERT INTO dbo.MyProducts (Name, ListPrice)
VALUES ('Helmet', 25.50),
       ('Wheel', 30.00),
       (SELECT Name, ListPrice FROM Production.Product WHERE ProductID = 720);
GO

하지만 하위 쿼리에서 각 열이 개별적으로 지정되도록 문을 다시 작성할 수 있습니다. 다음 예에서는 세 개의 행을 MyProducts 테이블에 삽입합니다.

INSERT INTO dbo.MyProducts (Name, ListPrice)
VALUES ('Helmet', 25.50),
       ('Wheel', 30.00),
       ((SELECT Name FROM Production.Product WHERE ProductID = 720),
        (SELECT ListPrice FROM Production.Product WHERE ProductID = 720));
GO

데이터 형식

다중 행 INSERT 문에 지정되는 값은 UNION ALL 구문의 데이터 형식 변환 속성을 따릅니다. 결과적으로 일치하지 않는 형식은 우선 순위가 높은 형식으로 암시적으로 변환됩니다. 이 변환이 암시적으로 지원되는 변환이 아니면 오류가 반환됩니다. 예를 들어 다음 문은 char 형식의 열에 정수 값과 문자 값 하나를 삽입합니다.

CREATE TABLE dbo.t (a int, b char);
GO
INSERT INTO dbo.t VALUES (1,'a'), (2, 1);
GO

INSERT 문을 실행하면 SQL Server에서는 'a'를 정수로 변환하려고 시도합니다. 데이터 형식 우선 순위에 따르면 정수가 문자보다 더 높은 형식이기 때문입니다. 이 변환은 실패하고 오류가 반환됩니다. 이 오류는 값을 명시적으로 적절하게 변환하면 피할 수 있습니다. 예를 들어 이전 문은 다음과 같이 작성할 수 있습니다.

INSERT INTO dbo.t VALUES (1,'a'), (2, CONVERT(CHAR,1));

예

1. 여러 데이터 행 삽입

다음 예에서는 테이블 dbo.Departments를 만든 다음 테이블 값 생성자를 사용하여 이 테이블에 5개의 행을 삽입합니다. 모든 열에 대한 값이 제공되고 값이 테이블 내의 열과 같은 순서로 나열되어 있으므로 열 목록에 열 이름을 지정할 필요가 없습니다.

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

2. DEFAULT 및 NULL 값을 사용하여 여러 행 삽입

다음 예에서는 테이블에 행을 삽입하는 데 테이블 값 생성자를 사용하는 경우 DEFAULT 및 NULL 지정에 대해 설명합니다.

USE AdventureWorks;
GO
CREATE TABLE Sales.MySalesReason(
    SalesReasonID int IDENTITY(1,1) NOT NULL,
    Name dbo.Name NULL ,
    ReasonType dbo.Name NOT NULL DEFAULT 'Not Applicable' );
GO
INSERT INTO Sales.MySalesReason 
VALUES ('Recommendation','Other'), ('Advertisement', DEFAULT), (NULL, 'Promotion');

SELECT * FROM Sales.MySalesReason;

3. FROM 절의 파생 테이블로 여러 값 지정

다음 예에서는 하나의 SELECT 문의 FROM 절에서 테이블 값 생성자를 사용하여 여러 값을 지정합니다.

SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);
GO

4. MERGE 문의 파생 원본 테이블로 여러 값 지정

다음 예에서는 MERGE를 사용하여 행을 업데이트하거나 삽입하는 방식으로 SalesReason 테이블을 수정합니다. 원본 테이블의 NewName 값이 대상 테이블 SalesReason의 Name 열에 있는 값과 일치하는 경우 대상 테이블에서 ReasonType 열이 업데이트됩니다. NewName 값이 일치하지 않는 경우 원본 행이 대상 테이블에 삽입됩니다. 원본 테이블은 Transact-SQL 테이블 값 생성자를 사용하여 원본 테이블의 여러 행을 지정하는 파생 테이블입니다.

USE AdventureWorks;
GO
-- Create a temporary table variable to hold the output actions.
DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));

MERGE INTO Sales.SalesReason AS Target
USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion'))
       AS Source (NewName, NewReasonType)
ON Target.Name = Source.NewName
WHEN MATCHED THEN
    UPDATE SET ReasonType = Source.NewReasonType
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)
OUTPUT $action INTO @SummaryOfChanges;

-- Query the results of the table variable.
SELECT Change, COUNT(*) AS CountPerChange
FROM @SummaryOfChanges
GROUP BY Change;

참고 항목

참조

INSERT(Transact-SQL)

MERGE(Transact-SQL)

FROM(Transact-SQL)