Guru: Table Value Constructors Build Tables On The Fly

June 25, 2018 Ted Holt

Because I wish to be as valuable and productive as possible to the people who pay me to program their computers, I continually search the Web for new ideas and techniques. Doing so often leads me to sites that cater to other computing platforms. Today I want to share with you some SQL techniques that I learned from Microsoft SQL Server professionals.

These techniques are based on the Table Value Constructor (TVC), which is a group of data values, usually literals, organized into rows and columns. In its simplest form, a table value constructor is literal tabular data that is not stored in a database table.

A table value constructor begins with the word VALUES, which is followed by one or more row value expressions.

Assume REPS, a table of sales representatives, which contains decimal columns REPID and REGION. We can use a table value constructor to add a group of sales reps.

insert into reps values
(11, 1), (12, 3), (17, 4),
(25, 2), (27, 2), (28, 4)

Six sales reps have been assigned to regions 1 through 4.

After a period of booming business and expansion into new parts of the globe, management revises the list of regions. This means that more region numbers are defined and some existing regions are assigned different region numbers. How would we go about reassigning the reps to the new region numbers?

We could build a table of old and new region numbers and use that table as the source in a MERGE statement, as I wrote about in Updating Through A Join, Take Three. If I don’t need such a table for other purposes, I can use a table value constructor instead, like this:

merge into reps as r
using (values (1, 1), (2, 6), (3,4), (4,2))
  as changes (OldRegion, NewRegion)
  on r.region = changes.OldRegion
when matched then
   update set r.Region = changes.NewRegion

The system reassigned the reps using the literal values in the TVC.

I learned something else that was interesting. I found out that I can use subselects instead of literals in table value constructors. Here’s a contrived example:

insert into reps values (
(select repid from shipments where shipmentid = 202), 4);

The REPID value is retrieved from shipment 202, and whatever rep that is gets assigned to region 4.

I haven’t use this technique in production yet, so if you do, please share your code with me. Also, if you have a TVC technique that I didn’t mention, please email me so I can share it with other readers.

I’ve yet to see the terms table value constructor and TVC used in IBM literature, but I expect IBM to start using it one of these days. After all, how else will they let SQL Server users know that DB2 also has this feature?

RELATED STORIES

Updating Through A Join, Take Three

Table Value Constructor (Transact-SQL)

Table Value Constructors in SQL Server 2008