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)

SSMS - Keyboard shortcut CTRL+K, CTRL+C not working

Keyboard shortcuts speed up development tasks in order to get more productive results. I encountered this problem that some keyboard shortcuts stopped working like CTRL+K, CTRL+C is not commenting the selected text, and it seemed unaffected. There could be two possible reasons which may cause this behavior:

• First check if you have any add-ins installed? Perhaps this is the cause of making default shortcuts not working as expected because an add-in has taken over the control.

• Second, check the keyboard scheme under Tools > Options > Environment > Keyboard. In my case, it has been switched to SQL Server 2000 you might try switching it back to Standard.