テーブル値コンストラクター (Transact-SQL)

  • [アーティクル]

テーブルに設定される行の値式のセットを指定します。Transact-SQL テーブル値コンストラクターを使用すると、単一の DML ステートメントで複数行のデータを指定できます。テーブル値コンストラクターは、INSERT ステートメントの VALUES 句、MERGE ステートメントの USING <ソース テーブル> 句、および 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 が挿入されます。DEFAULT は ID 列には有効ではありません。テーブル値コンストラクターで指定する場合、DEFAULT は INSERT ステートメント内でのみ使用できます。

  • expression
    定数、変数、または式を指定します。式には EXECUTE ステートメントを含めることができません。

制限事項と制約事項

テーブル値コンストラクターは、INSERT … VALUES ステートメントの VALUES リストで直接使用する方法と、派生テーブルが許容される箇所で派生テーブルとして使用するという 2 つの方法のいずれかで使用できます。VALUES リストに直接行を挿入して作成できる行の最大数は 1000 です。その場合に行の数が 1000 を超えると、エラー 10738 が返されます。1000 行を超える行を挿入するには、次のいずれかの方法を使用します。

  • 複数の INSERT ステートメントを作成する

  • 派生テーブルを使用する

  • bcp ユーティリティまたは BULK INSERT ステートメントを使用してデータを一括インポートする

単一のスカラー値だけが行の値式として使用できます。複数の列が関係するサブクエリは行の値式として使用できません。たとえば、次のコードでは、3 番目の行の値式のリストに複数の列を持つサブクエリが含まれているため、構文エラーが返されます。

USE AdventureWorks2012;
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 テーブルに 3 つの行が正常に挿入されます。

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 AdventureWorks2012;
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 AdventureWorks2012;
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 句で複数の値を派生テーブルとして指定する

次の例では、テーブル値コンストラクターを使用して SELECT ステートメントの FROM 句で複数の値を指定します。

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 の値が対象テーブル (SalesReason) の Name 列の値と一致すると、対象テーブルの ReasonType 列が更新されます。NewName の値が一致しない場合は、ソース行が対象テーブルに挿入されます。ソース テーブルは、Transact-SQL テーブル値コンストラクターを使用して複数の行を指定する派生テーブルです。

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