Select from list of values - Table Value Constructor

Table Value Constructor provides a way to construct a table from row value expressions. It allows multiple rows of data to be specified in a single DML statement. One simpler version of select statement with single row values may be used like this:

select 1 as Id, 'ST0258' as ItemCode, 'LASER JET TONER HP 1320 (49A)' as ItemName

But what if we need to select multiple rows with value literals, we need Table Value Constructor. Lets see how we can write similar select statement but with multiple rows.

SELECT Id, ItemCode, ItemName
FROM
  (
    VALUES
  (1, 'ST0258', 'LASER JET TONER HP 1320 (49A)'),
  (2, 'ST0259', 'LASER JET TONER HP 1000 (15A)'),
  (3, 'ST0260', 'LASER JET TONER HP P1005 (35A)'),
  (4, 'ST0261', 'LASER JET TONER HP 540 (CM1312)'),
  (5, 'ST0262', 'LASER JET TONER HP P2035 (05A)')
  )
  AS MyTableName (Id, ItemCode, ItemName)

Its a really cool technique and helps me to save my time while working with small chunks of data or debugging scenarios.

You can also use subqueries to construct row values, but the limitation is that only single scalar values are allowed as a row value expression to construct a single cell value. A subquery that involves multiple columns is not allowed as a row value expression, i.e., you can not specify a subquery with more than one columns to construct multiple cells of a row. For example, the following code will generate syntax error because it contains a subquery constructing more than one columns.

SELECT Id, ItemCode, ItemName
FROM
  (
    VALUES
  (1, 'ST0258', 'LASER JET TONER HP 1320 (49A)'),
  (2, 'ST0259', 'LASER JET TONER HP 1000 (15A)'),
  (3, 'ST0260', 'LASER JET TONER HP P1005 (35A)'),
  (4, 'ST0261', 'LASER JET TONER HP 540 (CM1312)'),
  (5, 'ST0262', 'LASER JET TONER HP P2035 (05A)'),
  (select Id,Item_Code,Item_Name from dbo.items where Item_Code='ST0263')
  )
  AS MyTableName (Id, ItemCode, ItemName)

However, you can rewrite the statement by specifying each column separately in the subquery. The following example code will execute successfully.

SELECT Id, ItemCode, ItemName
FROM
  (
    VALUES
  (1, 'ST0258', 'LASER JET TONER HP 1320 (49A)'),
  (2, 'ST0259', 'LASER JET TONER HP 1000 (15A)'),
  (3, 'ST0260', 'LASER JET TONER HP P1005 (35A)'),
  (4, 'ST0261', 'LASER JET TONER HP 540 (CM1312)'),
  (5, 'ST0262', 'LASER JET TONER HP P2035 (05A)'),
  (
   (select Id from dbo.items where item_code='ST0263'),
   (select Item_Code from dbo.items where item_code='ST0263'),
   (select Item_Name from dbo.items where item_code='ST0263')
  )
  )
  AS MyTableName (Id, ItemCode, ItemName)

References:

• Table Value Constructor (Transact-SQL)