Beginning with SQL Server 2008, you can define table value constructors (TVCs) within your data manipulation language (DML) statements so you can work with subsets of data in a tabular format. A TVC is an expression made up of a VALUES clause and one or more row value expressions, each of which returns a scalar value. Taken together, these values provide data to the DML statement in much the same way a subquery might return a table.

A TVC is made up of one or more columns and one or more rows of data. You can use a TVC as part of the VALUES clause in an INSERT statement, the USING clause in a MERGE statement, or the FROM clause in any DML statement. The following syntax shows the elements that make up a TVC:

Each TVC begins with the VALUES keyword, followed by one or more row value expressions (the part of the syntax enclosed in curly brackets). A row value expression can return only one value. You arrive at that value by defining an expression, specifying the NULL keyword (to return a null value), or specifying the DEFAULT keyword (to insert the default value in the column being targeted).

For each value you want to return in a TVC row, you must include a row value expression. If there are more than one row value expressions in a row, you separate them with commas. For each row you want to return, you must enclose the row value expressions in parentheses and separate the rows with commas, if there is more than one row. Essentially, you are using a TVC to construct a table made up of values that are organized into columns and rows. That means the number of values must be the same for each row you define.

The best way to understand how to create TVCs is to see them in action, so let’s look at a few examples. Note that all the examples target the Product table, which I created by using the following Transact-SQL code:

I created the Product table in the AdventureWorks2008R2 database on a local instance of SQL Server 2008 R2. The examples either insert data into the table or return data from that table.

NOTE: The examples are presented in the order I ran them. If you run them in a different order, you’ll get different results. For instance, the first few examples insert data into the Product table, followed by examples that retrieve the data. If you try to retrieve the data before you insert it, your SELECT statements will return no rows.

Using a Table Value Constructor in an INSERT Statement

The first example we’ll look at uses a TVC in an INSERT statement to add several rows of data to the Product table:

The first thing you’ll notice is that this looks a lot like your traditional INSERT statement, except that there are multiple sets of parentheses in the VALUES clause. The VALUES clause, not surprisingly, begins with the VALUES keyword and is followed by the data to be inserted into the Product table. Each set of parentheses represents a row of data, and each row (enclosed in the parentheses) includes the individual row value expressions.

For example, the first row includes three row value expressions: Mountain-300, 101, and 12. In this case, the expressions are all constants. Notice that the string value is enclosed in single quotes and the three values are separated by commas. In addition, a comma is used after the closing parenthesis to separate this row from the next.

You’ll wonder how you got along before having such a useful construct. Reasonably well, it turns out, since the code:

…is equivalent and runs on all versions of SQL Server.

When the TVC is taken as a whole, what you have is a table that includes three columns and seven rows of data. The INSERT statement will add these seven rows of data to the Product table. If you were then to retrieve the data from the table (via the SELECT statement I’ve included in the example), you’d get the following results:

ProductID	ProductName	CategoryID	ProductAmt
1	Mountain-300	101	12
2	Mountain-400	101	8
3	Road-250	101	8
4	Road-350	101	14
5	Helmet-sport	102	16
6	AWC Logo Cap	103	11
7	AWC Logo Jersey	103	18

As you can see, the data have been added to the ProductName, CategoryID, and ProductAmt columns. ProductID is an IDENTITY column, so the database engine assigned those values automatically.

That’s all there is to using a TVC in your INSERT statement. However, as you’ll recall from the TVC syntax above, you can also pass in the DEFAULT and NULL keywords for your row value expressions. For example, the following INSERT statement uses both of these keywords when adding three more rows to the Product table:

Notice that in the first row of the TVC, I’ve included the DEFAULT keywords as the second row value expression. In the third row, I’ve included the NULL keyword as the third row value expression. The SELECT statement now returns the following results:

ProductID	ProductName	CategoryID	ProductAmt
1	Mountain-300	101	12
2	Mountain-400	101	8
3	Road-250	101	8
4	Road-350	101	14
5	Helmet-sport	102	16
6	AWC Logo Cap	103	11
7	AWC Logo Jersey	103	18
8	Mountain-500	101	10
9	Road-450	101	6
10	Helmet-racing	102	NULL

As you would expect, the three rows have been added to the table. The row with the ProductID value of 8 has a CategoryID value of 101, which is the default value defined on that column. For the row with the ProductID value of 10, the ProductAmt value has been set to null. The use of the DEFAULT keyword is a useful feature that was not available to the old syntax that used UNION ALL.

So far, the row value expressions shown in the preceding two examples have been made up only of constants or the DEFAULT and NULL keywords. However, your row value expression can also be a variable or a more complex expression. For example, in the following INSERT statement, I include a subquery as one of the row value expressions:

As you can see, the second row value expression in the second row of the TVC is a subquery that returns a scalar value-the category ID of the product Helmet-racing.

NOTE: The subquery assumes that the product names are unique, in which case only one value is returned. I could have also specified the DISTINCT keyword, but that assumes that, if there are multiple products with the same name, they would always be associated with the same category ID. I would do better to use TOP 1 categoryID or MAX (CategoryID) to ensure a scalar subquery. For the purposes of this example, we’ll assume that the subquery will return only one value-or that we can rewrite it to ensure that it does.

The subquery’s value is then included in the TVC results just like the other row value expressions and inserted into the table. The SELECT statement I’ve tagged on to the example above returns the following results:

ProductID	ProductName	CategoryID	ProductAmt
1	Mountain-300	101	12
2	Mountain-400	101	8
3	Road-250	101	8
4	Road-350	101	14
5	Helmet-sport	102	16
6	AWC Logo Cap	103	11
7	AWC Logo Jersey	103	18
8	Mountain-500	101	10
9	Road-450	101	6
10	Helmet-racing	102	NULL
11	Mountain-600	101	10
12	Helmet-touring	102	14

As you can see, the Helmet-racing product has a CategoryID value of 102. That same value is used as the category ID for the Helmet-touring product because that’s the ID returned by the subquery.

Using a Table Value Constructor in a SELECT Statement

The examples above provide you with all the basics you need to build a TVC. However, you’re not limited only to the VALUES clause of an INSERT statement. In the following SELECT statement, I use a TVC as the table expression in the statement’s FROM clause:

As you can see, the TVC has the same format you saw in the preceding examples: each row is enclosed in a set of parentheses, and the two values in each row are separated with a comma. As a result, the TVC returns a table that includes two columns and three rows.

The SELECT statement can now use the TVC as it would any other table in the FROM clause. Because the SELECT clause specifies that all columns should be returned by the statement and there are no other qualifiers to limit the results (such as a WHERE clause), the statement returns all the data defined in the TVC, as shown in the following results:

CategoryID	CategoryName
101	Bikes
102	Accessories
103	Clothes

As you would expect, the SELECT statement returns a result set made up of two columns named CategoryID and CategoryName and three rows of data.

Using a TVC in a FROM gets much more interesting when you include other table expressions in that clause. For example, the following SELECT statement defines an inner join that includes the Product table and the TVC you saw in the preceding example:

Notice that I’ve defined the join based on the CategoryID column in the Product table and in the TVC. In addition, I’ve included only two columns in the SELECT list: the ProductName column from the Product table and the CategoryName column from the Category TVC. The SELECT statement returns the following results:

ProductName	CategoryName
AWC Logo Cap	Clothes
AWC Logo Jersey	Clothes
Helmet-racing	Accessories
Helmet-sport	Accessories
Helmet-touring	Accessories
Mountain-300	Bikes
Mountain-400	Bikes
Mountain-500	Bikes
Mountain-600	Bikes
Road-250	Bikes
Road-350	Bikes
Road-450	Bikes

As you would expect, the SELECT statement matched the product names to the category names. The TVC, in this case, acted just like any other table expression you could have defined in the FROM clause. Once again, this functionality has been in previous versions of SQL Server, but with the slightly more awkward syntax of UNION ALL.

Using a Table Value Constructor in a MERGE Statement

Another interesting way to use a TVC is within the USING clause of a MERGE statement. For example, in the following MERGE statement, I use a TVC to either update data in the Product table or insert data into that table:

The TVC defines fours rows of data with three columns each (ProductName, CategoryID, and ProductAmt). The TVC serves as the source table that contains the values necessary to modify the target table (Product). The MERGE statement compares the ProductName values in the source table with the ProductName values in the target table. (Again, this assumes that product names are unique.) If the product names match, the ProductAmt column is updated but the rest of the row is untouched. If the product names don’t match, the TVC row is inserted into the table.

After running the MERGE statement, the SELECT statement returns the following results:

ProductID	ProductName	CategoryID	ProductAmt
1	Mountain-300	101	17
2	Mountain-400	101	8
3	Road-250	101	13
4	Road-350	101	14
5	Helmet-sport	102	16
6	AWC Logo Cap	103	11
7	AWC Logo Jersey	103	18
8	Mountain-500	101	10
9	Road-450	101	6
10	Helmet-racing	102	15
11	Mountain-600	101	10
12	Helmet-touring	102	14
13	Mountain-700	101	7
14	Road-550	101	9

It turns out that the MERGE statement updated the ProductAmt values for the Mountain-300, Road-250, and Helmet-racing products. However, the statement added rows for the Mountain-700 and Road-550 products because those products did not match any of the product names that already existed within the table.

Conclusion

As you can see, TVCs can be used in a number of ways within your DML statements. Keep in mind, however, that a TVC is limited to 1000 rows, and, as stated earlier, each row value expression must return a scalar value, otherwise you’ll receive a syntax error when you try to run your statement. TVCs represent a simplification of syntax rather than new functionality. You can do almost everything you can with a TVC in previous versions of SQL Server, barring the use of the DEFAULT keyword.

You can find more information about TVCs by referring to the topic “Table Value Constructor (Transact-SQL)” in SQL Server Books Online. There you will also find additional examples of the various ways to use TVCs.