Click here to monitor SSC
  • Av rating:
  • Total votes: 61
  • Total comments: 8
Robert Sheldon

Table Value Constructors in SQL Server 2008

22 June 2011

Table Value Constructors (TVCs) are a useful feature of 2008, allowing you to specify tables of values and expressions. This has all sorts of uses. Users who are stuck with previous versions of SQL Server can play along, since Rob demonstrates that there have, for a long time, been ways of doing this in SQL Server, though less elegantly.

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:

VALUES( {DEFAULT|NULL|<expression>} [,...n] ) [,...n]

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:

USE AdventureWorks2008R2;

GO

 

IF OBJECT_ID ('Product', 'U') IS NOT NULL

DROP TABLE dbo.Product;

 

CREATE TABLE dbo.Product

(

  ProductID INT IDENTITY(1,1) NOT NULL,

  ProductName NVARCHAR(50) NOT NULL,

  CategoryID INT NOT NULL DEFAULT 101,

  ProductAmt INT NULL

);

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:

INSERT INTO dbo.Product

  (ProductName, CategoryID, ProductAmt)

VALUES

  ('Mountain-300', 101, 12),

  ('Mountain-400', 101, 8),

  ('Road-250', 101, 8),

  ('Road-350', 101, 14),

  ('Helmet-sport', 102, 16),

  ('AWC Logo Cap', 103, 11),

  ('AWC Logo Jersey', 103, 18);

 

SELECT * FROM Product;

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:

INSERT INTO dbo.Product

  (ProductName, CategoryID, ProductAmt)

  SELECT 'Mountain-300', 101, 12

  UNION ALL SELECT 'Mountain-400', 101, 8

  UNION ALL SELECT 'Road-250', 101, 8

  UNION ALL SELECT 'Road-350', 101, 14

  UNION ALL SELECT 'Helmet-sport', 102, 16

  UNION ALL SELECT 'AWC Logo Cap', 103, 11

  UNION ALL SELECT 'AWC Logo Jersey', 103, 18

…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:

INSERT INTO dbo.Product

  (ProductName, CategoryID, ProductAmt)

VALUES

  ('Mountain-500', DEFAULT, 10),

  ('Road-450', 101, 6),

  ('Helmet-racing', 102, NULL);

 

SELECT * FROM Product;

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:

INSERT INTO dbo.Product

  (ProductName, CategoryID, ProductAmt)

VALUES

  ('Mountain-600', 101, 10),

  ('Helmet-touring',

    (

      SELECT CategoryID

      FROM Product

      WHERE ProductName = 'Helmet-racing'

    ),

    14);

 

SELECT * FROM Product;

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:

SELECT *

FROM

  (VALUES

    (101, 'Bikes'),

    (102, 'Accessories'),

    (103, 'Clothes')

  ) AS Category(CategoryID, CategoryName);

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:

SELECT

  Product.ProductName,

  Category.CategoryName

FROM

  Product INNER JOIN

  (VALUES

    (101, 'Bikes'),

    (102, 'Accessories'),

    (103, 'Clothes')

  ) AS Category(CategoryID, CategoryName)

  ON Product.CategoryID = Category.CategoryID

ORDER BY

  Product.ProductName;

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.

SELECT

  Product.ProductName,

  Category.CategoryName

FROM

  Product INNER JOIN

  (SELECT 101, 'Bikes'

    UNION ALL SELECT 102, 'Accessories'

    UNION ALL SELECT 103, 'Clothes'

  ) AS Category(CategoryID, CategoryName)

  ON Product.CategoryID = Category.CategoryID

ORDER BY

  Product.ProductName;

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:

MERGE INTO dbo.Product AS Target

USING

  (VALUES

  ('Mountain-300', 101, 17),

  ('Mountain-700', 101, 7),

  ('Road-250', 101, 13),

  ('Road-550', 101, 9),

  ('Helmet-racing', 102, 15)

  ) AS Source(ProductName, CategoryID, ProductAmt)

ON Target.ProductName = Source.ProductName

WHEN MATCHED THEN

  UPDATE SET Target.ProductAmt = Source.ProductAmt

WHEN NOT MATCHED BY TARGET THEN

  INSERT (ProductName, CategoryID, ProductAmt)

  VALUES (Source.ProductName, Source.CategoryID, Source.ProductAmt);

 

SELECT * FROM Product;

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.

Robert Sheldon

Author profile:

After being dropped 35 feet from a helicopter and spending the next year recovering, Robert Sheldon left the Colorado Rockies and emergency rescue work to pursue safer and less painful interests—thus his entry into the world of technology. He is now a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. He has also written news stories, feature articles, restaurant reviews, legal summaries, and the novel 'Dancing the River Lightly'. You can find more information at http://www.rhsheldon.com.

Search for other articles by Robert Sheldon

Rate this article:   Avg rating: from a total of 61 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Nice one.
Posted by: David McKinney. (not signed in)
Posted on: Monday, June 27, 2011 at 7:24 AM
Message: Thanks Robert for the clear roundup.

This is the first time I've seen UNION ALL written in this way.

SELECT 101, 'Bikes'
UNION SELECT ALL 102, 'Accessories'
UNION SELECT ALL 103, 'Clothes'

I've always written it as

SELECT 101, 'Bikes'
UNION ALL SELECT 102, 'Accessories'
UNION ALL SELECT 103, 'Clothes'

hmmm....not sure which one I prefer.

Subject: aha...confused now.
Posted by: Anonymous (not signed in)
Posted on: Monday, June 27, 2011 at 7:28 AM
Message: I see now that it's not the same thing!

select all 1
UNION
select all 1 -- returns 1 row whereas

select all 1
UNION all
select all 1 -- returns 2 rows.



Subject: aha...confused now.
Posted by: David McKinney. (not signed in)
Posted on: Monday, June 27, 2011 at 7:32 AM
Message: If I'm not mistaken, you should have written

SELECT 101, 'Bikes'
UNION ALL SELECT 102, 'Accessories'
UNION ALL SELECT 103, 'Clothes'

as the ALL of the type "SELECT ALL 102, 'Accessories'" has no effect. It's the default alternative to DISTINCT.

Subject: Re confused
Posted by: Andrew Clarke (view profile)
Posted on: Monday, June 27, 2011 at 8:38 AM
Message: That was a wierd corruption in the setting phase of the article. Fixed now: Don't even ask how that crept in but all code was checked and executed beforehand!

Subject: ... and they talk about "portability". ;-)
Posted by: Jeff Moden (view profile)
Posted on: Monday, June 27, 2011 at 6:17 PM
Message: Nice article, Robert. Well done.

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

That's exactly why I don't use TVC's in any of my posts on SQLServerCentral especially on really simple coded answers. Since 2k5 and 2k8 have a lot of the same functionality everywhere else, it just makes life easier on everyone.



Subject: bujjinan@gmail.com
Posted by: Articles (not signed in)
Posted on: Wednesday, June 29, 2011 at 3:52 AM
Message: using your article i definitely,knew a lot of new things about table constructions.. really very good information given to all of us....
Thanking you...
_____________________
saritha.

Subject: Awesome...
Posted by: Keith (view profile)
Posted on: Thursday, June 30, 2011 at 2:09 PM
Message: I was using an IN statement with a list of values. I replaced the IN statement with a JOIN to a VALUES statement and a list of my values. Query returned in 30 seconds as opposed to before where it just spun for two minutes.

Awesome.

Subject: cool
Posted by: Thiago Dantas (not signed in)
Posted on: Thursday, July 07, 2011 at 8:30 AM
Message: never knew you could use VALUES inside the from clause, seems like a weird syntax but nice to know its there

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.