|
|
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
|