資料表值建構函式 (Transact-SQL)

發行項
03/07/2014

指定要建構到資料表中的一組資料列值運算式。 Transact-SQL 資料表值建構函式允許在單一 DML 陳述式中指定多個資料列。資料表值建構函式可以在 INSERT 陳述式的 VALUES 子句、MERGE 陳述式的 USING <來源資料表> 子句以及 FROM 子句的衍生資料表定義中指定。

適用於：SQL Server (SQL Server 2008 透過目前版本)、Windows Azure SQL 資料庫 (初始版本，透過目前版本)。

主題連結圖示 Transact-SQL 語法慣例

語法

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

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

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

引數

VALUES
導入資料列值運算式清單。每一個清單都必須以括號括住，並以逗點隔開。

每一個清單中指定的值數目都必須相同，而且值的順序必須與資料表中的資料行相同。必須指定資料表中每一個資料行的值，或者資料行清單必須明確指定每一個內送值的資料行。
DEFAULT
強制 Database Engine 插入定義給資料行的預設值。如果資料行的預設值不存在，而且資料行允許 Null 值，就會插入 NULL。 DEFAULT 對識別欄位無效。在資料表值建構函式中指定時，INSERT 陳述式中只允許 DEFAULT。
expression
這是一個常數、變數或運算式。此運算式不能包含 EXECUTE 陳述式。

限制事項

資料表值建構函式的使用方式有兩種：直接用於 INSERT … VALUES 陳述式的 VALUES 清單中，或是在允許使用衍生資料表的任何位置當做衍生資料表使用。可直接在 VALUES 清單中插入資料列藉以建構的最大資料列數目為 1000。在該情況下，如果資料列數目超過 1000，就會傳回錯誤 10738。若要插入 1000 個以上的資料列，請使用下列其中一種方法：

建立多個 INSERT 陳述式
使用衍生資料表
使用 bcp 公用程式或 BULK INSERT 陳述式，大量匯入資料

只允許使用單一純量值當做資料列值運算式。不允許使用牽涉多個資料行的子查詢當做資料列值運算式。例如，下列程式碼會產生語法錯誤，因為第三個資料列值運算式清單包含具有多個資料行的子查詢。

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 資料表中。

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 資料表，然後使用資料表值建構函式將五個資料列插入此資料表。由於提供了所有資料行的值，而且依照資料表中資料行的相同順序來列出它們，因此，不需要在資料行清單中指定資料行名稱。

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)

共用方式為