loading..
Ðóññêèé    English
19:06
go

Table-values constructor

Table-values constructor syntax:

  1. VALUES 
  2. (< constructor element >, < constructor element >, ...),
  3. (< constructor element >, < constructor element >, ...),
  4. ...
  5. (< 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:

  1. CREATE TABLE Items (
  2. item_no int PRIMARY KEY,
  3. maker char(10),
  4. type char(10) DEFAULT 'PC',
  5. value int
  6. );

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

  1. INSERT INTO Items VALUES
  2. (1, 'A', 'Laptop', 12),
  3. (2, 'B', DEFAULT, NULL),
  4. (3, 'C', 'Printer', (SELECT CAST(model AS int) FROM Printer WHERE code=1)),
  5. (4, 'C', 'Printer', (SELECT CAST(model AS int) FROM Printer WHERE code=77));

  1. SELECT * FROM Items;

item_no    maker    type    value
1    A             Laptop        12
2    B             PC            NULL
3    C             Printer       3001
4    C             Printer       NULL

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:

Console
Execute
  1. SELECT (SELECT MAX(model)
  2.     FROM Product
  3.     ) + 5*5*(a-1) + 5*(b-1) + c AS num
  4.     FROM 
  5.     (VALUES(1),(2),(3),(4),(5)) x(a) CROSS JOIN
  6.     (VALUES(1),(2),(3),(4),(5)) y(b)  CROSS JOIN
  7.     (VALUES(1),(2),(3),(4),(5)) z(c)
  8.     WHERE 5*5*(a-1) + 5*(b-1) + c <= 100
  9.     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.

Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.