Table Value Constructor (Transact-SQL)
Table Value Constructor (TVC), sometimes called Row Constructor, let you create a table expression simply by specifying a list of comma separated rows using a VALUES clause . Table Value Constructors are a feature that was added in SQL Server 2008. You can define these table expressions within your DML statements in much the same way as any other table expression might be used. The VALUES clause is the key to the Transact-SQL statements ability to utilize multiple rows in a single statement. You can use the VALUES clause in an INSERT statement, as the <source> in a MERGE statement and also as a derived table object used by a FROM clause.
The following example creates a table named dbo.Products. The Table Value Constructor uses the VALUES clause to produce table expression in the form of a list. Each row must be enclosed in parenthesis and separated by a comma. This particular table expression inserts 8 rows from a list of rows in the VALUE clause. Note the last row was even able to specify a NULL placeholder for one of the values.
CREATE DATABASE EraseMe; GO USE Eraseme; GO CREATE TABLE dbo.Products ( ProductID INT IDENTITY(1,1) NOT NULL, ProductName NVARCHAR(50) NOT NULL, VendorID INT NOT NULL DEFAULT 101, UnitPrice money NULL ); --Table Valued Constructor in an INSERT Statement Adding multiple rows INSERT INTO dbo.Products (ProductName, VendorID, UnitPrice) VALUES ('1996 Moto Guzzi 1100i', 1010, 44.78), ('1962 LanciaA Delta 16V', 1050, 84.99), ('1968 Ford Mustang', 1060, 89.99), ('1958 Setra Bus', 1020, 142.50), ('1969 Ford Falcon', 1010, 161.25), ('1928 Mercedes-Benz SSK', 1030, 110.99), ('P-51-D Mustang', 1040, 185.99), ('1957 Vespa GS150', 1040, NULL); SELECT * FROM Products;
The following TVC example use the VALUES clause to construct a derived table in the FROM clause and builds a INNER JOIN to the previously created table.
--TVC used as a Derived Table in the FROM clause SELECT p.ProductID, v.Productname FROM dbo.Products AS p INNER JOIN ( VALUES (1010,'1996 Moto Guzzi 1100i'), (1020, '1958 Setra Bus'), (1030,'1928 Mercedes-Benz SSK') ) AS v (Vendorid,Productname) ON p.vendorid = v.vendorid AND p.ProductName = v.ProductName;
Table value constructors can be used in a couple of different ways.
- Directly in the VALUES list of an INSERT … VALUES statement
- Derived table anywhere that derived tables can be created
The maximum number of rows that can be constructed by inserting rows directly in the VALUES list is 1000. Error 10738 is returned if the number of rows exceeds 1000 in that case. To insert more than 1000 rows, use one of these workarounds:
- Create multiple INSERT statements or
- Use the VALUE clause as a derived table like we did in the above example.
As usual I had fun writing this quick intro to the Table Value Constructor. Hopefully it was fun to read as well. And finally here is something for you to think about.
~ Its so hot the chickens are laying hard-boiled eggs.