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

적용 대상: Microsoft Fabric의 SQL ServerAzure SQL Database Azure SQL Managed InstanceSQL 데이터베이스

테이블에 생성할 행 값 식의 집합을 지정합니다. Transact-SQL 테이블 값 생성자를 사용하면 단일 DML 문에 여러 데이터 행을 지정할 수 있습니다. 테이블 값 생성자는 문의 절 또는 VALUES 문 절 INSERT ... VALUES 에서 파생 테이블 USINGMERGEFROM 로 지정할 수 있습니다.

Transact-SQL 구문 표기 규칙

구문

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

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

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

인수

Values

행 값 식 목록을 나타냅니다. 각 목록은 괄호로 묶고 쉼표로 구분해야 합니다.

각 목록에는 같은 수의 값을 지정해야 하고 이러한 값의 순서는 테이블의 열 순서와 같아야 합니다. 테이블의 각 열에 대해 값을 지정하거나, 들어오는 각 값을 위한 열을 열 목록에서 명시적으로 지정해야 합니다.

기본값

데이터베이스 엔진에서 열에 대해 정의된 기본값을 삽입하도록 설정합니다. 열에 대한 기본값이 없으며 열에서 null 값을 NULL 허용하는 경우 삽입됩니다. DEFAULT 이 ID 열에 유효하지 않습니다. 테이블 값 생성자에 DEFAULT 지정된 경우 문에서 INSERT 만 허용됩니다.

표현식

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

제한점

파생 테이블로 사용하는 경우 행 수에 제한이 없습니다.

문의 절 VALUES 로 INSERT ... VALUES 사용하는 경우 1,000개의 행으로 제한됩니다. 행 수가 최대값을 초과하면 오류 10738이 반환됩니다. 1,000개 이상의 행을 삽입하려면 다음 방법 중 하나를 사용합니다.

• 여러 INSERT 문을 만듭니다.

• 파생 테이블을 사용합니다.

• bcp 유틸리티, .NET SqlBulkCopy 클래스, OPENROWSET BULK 또는 BULK INSERT 문을 사용하여 데이터를 대량으로 가져옵니다.

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

USE AdventureWorks2022;  
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));  

예제

A. 여러 행의 데이터 삽입

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

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

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

다음 예제에서는 테이블 값 생성자를 사용하여 테이블에 행을 삽입하는 경우를 지정 DEFAULT 하고 NULL 사용하는 방법을 보여 줍니다.

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

C. FROM 절에서 여러 값을 파생 테이블로 지정

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

SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);  
GO  
-- Used in an inner join to specify values to return.  
SELECT ProductID, a.Name, Color  
FROM Production.Product AS a  
INNER JOIN (VALUES ('Blade'), ('Crown Race'), ('AWC Logo Cap')) AS b(Name)   
ON a.Name = b.Name;  

D. MERGE 문에서 여러 값을 파생 원본 테이블로 지정

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

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

E. 1,000개 이상의 행 삽입

다음 예제에서는 테이블 값 생성자를 파생 테이블로 사용하는 방법을 보여줍니다. 이렇게 하면 단일 테이블 값 생성자에서 1,000개 이상의 행을 삽입할 수 있습니다.

CREATE TABLE dbo.Test ([Value] INT);  

INSERT INTO dbo.Test ([Value])  
  SELECT drvd.[NewVal]
  FROM   (VALUES (0), (1), (2), (3), ..., (5000)) drvd([NewVal]);

관련 콘텐츠

• INSERT(Transact-SQL)
• MERGE(Transact-SQL)
• FROM 절과 JOIN, APPLY, PIVOT(Transact-SQL)