For such cases you can either create a dataset on the fly with a set of UNIONS and reuse it in queries or, create an UPDATE statement for each pair of values. You can even automate the creation of UNION and UPDATE statements, however their use doesn’t seem so elegant, especially when you can use table value constructors. The feature seems to be hidden in documentation, as a matter of fact I found it only in the online documentation, the local help having no reference to it. Anyway, excepting a few posts on the same subject I can’t say I have seen any code until now making use of it. What’s interesting to note, before going into details, is that the ANSI SQL 94 (the earliest version of documentation I have) mentions vaguely table value constructors (p. 221), therefore I expect they are part of ANSI standard.
According to MSDN a table value constructor allows a set of row value expressions to be constructed into a table within a single DML statement. As can be seen from the MSDN examples the list of values can contain a combination of list of values and queries, as long the data type matches and only a single scalar value is provided as a row value expression Another limitation concerns the maximum number of rows constructed, the maximum value being 1000. It’s important to know these limitations, because they might force you use other approaches. Before sketching a solution for this post’s problem, let’s look how a table can be constructed on the fly given a list of pair-values:
-- example table value constructor SELECT EmployeeId , VacationHours FROM ( VALUES (101, '23') , (102, '82') , (103, '66') , (104, '17') , (105, '64') , (106, '56') , (107, '107') , (108, '50') , (109, '109') , (110, '48')) AS Data(EmployeeId, VacationHours)
The table thus constructed can be further used in an UPDATE statement, the following example being based on HumanResources.Employee from AdventureWorks database.
-- example table value constructor update UPDATE HumanResources.Employee SET VacationHours = Data.VacationHours FROM ( VALUES (101, '23') , (102, '82') , (103, '66') , (104, '17') , (105, '64') , (106, '56') , (107, '107') , (108, '50') , (109, '109') , (110, '48')) AS Data(EmployeeId, VacationHours) WHERE HumanResources.Employee.EmployeeId = Data.EmployeeId
A similar construct can be be used to perform a SELECT, UPDATE or DELETE in which multiple columns participate in the JOIN. For example let’s consider a SELECT based on a set of pairs of values from the Sales.SalesOrderHeader table:
-- example table value constructor SELECT SOH.* FROM Sales.SalesOrderHeader SOH JOIN ( -- set of pairs VALUES(10, 5069) , (10, 6070) , (10, 7060) , (11, 407) , (11, 1603) , (11, 2737)) AS Data(CustomerId, CurrencyRateID) ON SOH.CustomerId = Data.CustomerID AND SOH.CurrencyRateID = Data.CurrencyRateID
As can be seen table value constructors are pretty simple to use, especially when dealing with a small number of values. If they are the best choice, that depends on the problem you are trying to solve and its specific constraints!
