Table-values constructor

Table-values constructor syntax:

VALUES
(< constructor element >, < constructor element >, ...),
(< constructor element >, < constructor element >, ...),
...
(< constructor element >, < constructor element >, ...)

Constructor element is one of the following:

• expression calculating a value, data type of which is compatible with the type of the corresponding table column;

• DEFAULT - for the substituting the default value of the corresponding table column;

• NULL;

• subquery that returns a single value, data type of which is compatible with the type of the corresponding table

column.

Table-values constructor can be used for the adding the rows set to an existing table with aid of INSERT statement.

Let’s create the following table for examples:

CREATE TABLE Items (
item_no int primary key,
maker char(10),
type char(10) default 'PC',
value int
);

Let’s insert four rows in the table using constructor.

INSERT INTO Items VALUES
(1, 'A', 'Laptop', 12),
(2, 'B', DEFAULT, NULL),
(3, 'C', 'Printer', (select CAST(model as int) from Printer where code=1)),
(4, 'C', 'Printer', (select CAST(model as int) from Printer where code=77));

select * from Items;

The last value in two last rows was obtained with aid of subquery which returns either single value (due to choosing the key value) of the model number from Printer table or none. The latter takes place for the fourth row because the code of 77 does not match any row in the Printer table. In this case NULL value is being written into the table.

Table-values constructor can be used in FROM clause also. In the topic about number-sequence generating, the last example, which finds the 100 sequential unused model numbers, can be rewritten in more compact form with aid of this feature:

SELECT (SELECT MAX(model)
    FROM Product
    ) + 5*5*(a-1) + 5*(b-1) + c AS num
    FROM
    (VALUES(1),(2),(3),(4),(5)) x(a) CROSS JOIN
    (VALUES(1),(2),(3),(4),(5)) y(b)  CROSS JOIN
    (VALUES(1),(2),(3),(4),(5)) z(c)
    WHERE 5*5*(a-1) + 5*(b-1) + c <= 100
    ORDER BY 1;

Another example of usage of table-values constructor for transforming a row into a column you can see in the chapter devoted to CROSS APPLY operator.