Table-values constructor syntax:
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;
- subquery that returns a single value, data type of which is compatible with the type of the corresponding table
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:
Let's insert four rows in the table using constructor.
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:
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.