Skip Navigation Links
Home
Functions
Tips & Tricks
SQL Server 2005
SQL Server 2008
SQL Server 2012
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Using CHECK Constraints to Validate Data (CHECK Constraint Examples)
Home > SQL Server 2008 > Row Constructor (or Table-Valued Constructor) as Derived Table
Row Constructor (or Table-Valued Constructor) as Derived Table

In the article entitled "Multiple Value Inserts Using a Single INSERT Statement", it discusses the new row constructor syntax introduced in SQL Server 2008 where using the VALUES clause, you can insert multiple records in a table using a single INSERT statement.

In this article, we will show how to use the same row value constructor as a derived table which you can use in the FROM clause of a SELECT statement and as a source table in a MERGE statement, yet another new statement introduced in SQL Server 2008.

The simplest way to use the row value constructor in a SELECT statement as a derived table can be illustrated in the following script:

SELECT * 
FROM (VALUES ('USD', 'U.S. Dollar'),
             ('EUR', 'Euro'),
             ('CAD', 'Canadian Dollar'),
             ('JPY', 'Japanese Yen')) AS [Currency] ( [CurrencyCode], [CurrencyName] )
             
CurrencyCode   CurrencyName
-------------- --------------------
USD            U.S. Dollar
EUR            Euro
CAD            Canadian Dollar
JPY            Japanese Yen

The first part is the VALUES clause where you specify the different column values grouped together within a parenthesis, with each group representing a row are separated by a comma.  The second part of the syntax is the alias assigned to the derived table, in this case it is called [Currency], followed by the names of the columns to be assigned to each column value provided in the VALUES clause.

Make sure that the number of column values provided in the VALUES clause has the same number of columns defined in the table alias.  If the number of columns enumerated in the column list has more columns than those specified in the VALUES clause, you will get the following error message:

Msg 8159, Level 16, State 1, Line 5
'Currency' has fewer columns than were specified in the column list.

Likewise, if the number of column values specified in the VALUES clause are more than the number of columns specified in the columns list, you will get the following error message:

Msg 8158, Level 16, State 1, Line 6
'Currency' has more columns than were specified in the column list.

Just like any derived table, the row value constructor derived table can also be used in table joins.  Here's a sample script that joins 2 row value constructor derived tables:

SELECT [MetricPrefix].[Prefix] + [Measure].[Unit] AS [Measurement], [MetricPrefix].[Multiplier]
FROM (VALUES ('milli', 0.001),
             ('centi', 0.01),
             ('deci', 0.1),
             ('kilo', 1000)) AS [MetricPrefix] ( [Prefix], [Multiplier] ),
     (VALUES ('gram'),
             ('liter'),
             ('meter')) AS [Measure] ( [Unit] )
GO

Measurement   Multiplier
------------  ------------
milligram     0.001
centigram     0.010
decigram      0.100
kilogram      1000.000
milliliter    0.001
centiliter    0.010
deciliter     0.100
kiloliter     1000.000
millimeter    0.001
centimeter    0.010
decimeter     0.100
kilometer     1000.000

Row Constructor (or Table-Valued Constructor) as Source in a MERGE Statement

The MERGE statement is a new Transact-SQL statement introduced in SQL Server 2008 which performs an INSERT, UPDATE or DELETE operation on a target table based on the results of a join with a source table.  A common scenario is in maintaining lookup tables wherein new records are inserted if the record does not exist yet in the table or updating certain columns if the record already exists in the table.  Prior to SQL Server 2008, you first have to check if the record exists in the lookup table.  If the record does not exist, you perform an INSERT statement, otherwise you do an UPDATE on the record for one or more columns.  With SQL Server 2008, you only now need to issue a single statement, the MERGE statement.  For more information about the MERGE statement, you can refer to the MERGE Statement article.

Here's a sample script that uses a row value constructor derived table as the source of the MERGE statement:

CREATE TABLE [dbo].[Currency] (
	[CurrencyCode]			CHAR(3),
	[CurrencyName]			VARCHAR(50)
)
GO

MERGE [dbo].[Currency] AS [Target]
USING (VALUES ('USD', 'U.S. Dollar'),
              ('EUR', 'Euro'),
              ('CAD', 'Canadian Dollar'),
              ('JPY', 'Japanese Yen')) 
  AS [Source] ( [CurrencyCode], [CurrencyName] )
  ON [Target].[CurrencyCode] = [Source].[CurrencyCode]
WHEN MATCHED THEN
    UPDATE SET [CurrencyName] = [Source].[CurrencyName]
WHEN NOT MATCHED THEN
    INSERT ( [CurrencyCode], [CurrencyName] )
    VALUES ( [Source].[CurrencyCode], [Source].[CurrencyName] );
GO

SELECT * FROM [dbo].[Currency]

CurrencyCode   CurrencyName
-------------- --------------------
USD            U.S. Dollar
EUR            Euro
CAD            Canadian Dollar
JPY            Japanese Yen
Related Articles :