By Nicholas Geiger – KMCDATA
I find the table value constructor to be quite useful at times. It allows one to form a multi-row table in a concise manner. It can be used in INSERT and MERGE statements, and as derived tables in the FROM clause. I’ve used it many times in data fix scripts and occasionally in procedures and views.
Suppose there is the table dbo.Ticket shown below.
create table Ticket
(
Row int,
PositionInRow int,
TicketId int primary key identity(1, 1)
);
Below is an example of how the table value constructor can be used in an INSERT statement. This statement will insert three rows into the dbo.Tickets table.
insert into dbo.Ticket(Row, PositionInRow)
values (2, 3),
(7, 4),
(16, 6);
Below is an example of how the table value constructor can be used to create a derived table in the FROM clause. This statement will create a result set of three rows with columns Row and PositionInRow. The results follow.
select *
from (values(2, 3),
(7, 4),
(16, 6)) as Ticket(Row, PositionInRow);
Row PositionInRow
2 3
7 4
16 6
One special use of this syntax is to add multiple columns onto existing rows in a table. For instance, suppose you have a table of plane tickets and you want to add columns BeveragePackage, FoodPackage, and EntertainmentPackage to each row, but different sets of values for certain row sets. You could write the following to accomplish this. The results follow.
select t.*, Package.BeveragePackage, Package.FoodPackage, Package.EntertainmentPackage
from dbo.Ticket t
inner join (values (1, 5, 1, 1, 1),
(6, 15, 1, 2, 2),
(16, 30, 2, 2, 3)) as Package(MinRow, MaxRow, BeveragePackage, FoodPackage, EntertainmentPackage)
on Package.MinRow <= t.Row
and Package.MaxRow >= t.Row;
Row PositionInRow TicketId BeveragePackage FoodPackage EntertainmentPackage
2 3 1 1 1 1
7 4 2 1 2 2
16 6 3 2 2 3
Another special use of this syntax is to multiply rows in a table. For instance, suppose you have a row of plane tickets and you want to display parts of it on different pages of some report. You could multiply the row by the number of pages that you want in the report by joining a derived table of page numbers to the Ticket row. The example below will create a result set containing three rows with all the values from the Ticket row with TicketId 1 and an additional column PageNumber. The results follow.
select *
from dbo.Ticket t
inner join (values(1), (2), (3)) as Page(PageNumber)
on 1 = 1
where t.TicketId = 1;
Row PositionInRow TicketId PageNumber
2 3 1 1
2 3 1 2
2 3 1 3
There may be even more uses for the table value constructor. So while you code, you may find the table value constructor to be a valuable enough tool to add to your own collection.