The query needs a tiny lookup list, but nobody wants another permanent table for it. A table constructor turns those values into rows inside the statement. Give the list names and types before joining it.
Give the Inline List Column Names
VALUES can define a derived table in the FROM clause. Each parenthesized group supplies one row. The alias and following column list give those values readable names.
Every row must supply the same number of expressions. Their positions must mean the same thing across rows. A mismatched position can be valid SQL with the wrong mapping.
The example below maps small status codes to descriptions. It doesn't create an object in the database. The rows exist as part of this statement's relational input.
I use this shape when the mapping is short and stable for the query. It keeps the rule visible beside its use. A shared business catalog needs a different home.
The returned order still requires ORDER BY. The written sequence of VALUES rows isn't the query's output-order contract. Sort explicitly when presentation order matters.
SELECT StatusCode, StatusName
FROM (VALUES
(1, CONVERT(varchar(20), 'Pending')),
(2, CONVERT(varchar(20), 'Approved')),
(3, CONVERT(varchar(20), 'Rejected'))
) AS status_map(StatusCode, StatusName)
ORDER BY StatusCode;Join a Table Constructor Like Any Other Relation
The constructed rows participate in ordinary joins. That makes them useful for labeling a result. The join still follows normal duplicate and missing-match rules.
The temporary table below contains sample work items. Its status column is an input for the mapping. One deliberate unmapped value lets you inspect the handling of missing definitions.
An INNER JOIN removes unmapped work items. A LEFT JOIN preserves them with a NULL description. Choose that behavior from the reporting requirement rather than convenience.
A fallback description can make unmapped values visible. It shouldn't hide a missing business catalog entry. Preserve the original code so someone can investigate it.
A table constructor doesn't enforce uniqueness in its mapping automatically. Duplicate status rows multiply join results. Review the list with the same care you give a stored lookup table.
CREATE TABLE #ConstructorItemsDemo(ItemId int PRIMARY KEY, StatusCode int NOT NULL);
INSERT #ConstructorItemsDemo VALUES(1, 1), (2, 2), (3, 9);
SELECT i.ItemId, i.StatusCode, COALESCE(m.StatusName, 'Unmapped') AS StatusName
FROM #ConstructorItemsDemo AS i
LEFT JOIN (VALUES
(1, CONVERT(varchar(20), 'Pending')),
(2, CONVERT(varchar(20), 'Approved')),
(3, CONVERT(varchar(20), 'Rejected'))
) AS m(StatusCode, StatusName) ON m.StatusCode = i.StatusCode
ORDER BY i.ItemId;Keep Table Constructor Types Consistent by Column
The constructor combines expression types by SQL Server's conversion rules. A numeric expression can outrank a character expression in the same position. That can cause an attempted conversion you didn't intend.
The destination table doesn't always rescue an inconsistent source list. Type resolution occurs within the constructor too. A text destination can still receive a failing numeric conversion upstream.
Use explicit conversions to establish the intended type. Cast NULL when its column contract isn't otherwise clear. Choose lengths and precision sufficient for every supplied value.
The next list deliberately mixes a numeric-looking identifier with a letter identifier. Both are declared text before combination. That keeps the business identifier contract stable.
I check types when the smallest inline list raises a conversion error. The trouble is usually in one expression position. Reading by column rather than by row exposes it.
SELECT CodeText, Amount
FROM (VALUES
(CONVERT(varchar(10), 12), CONVERT(decimal(12,2), 4.50)),
(CONVERT(varchar(10), 'A12'), CONVERT(decimal(12,2), 5.00)),
(CONVERT(varchar(10), NULL), CONVERT(decimal(12,2), 0.00))
) AS v(CodeText, Amount);
Distinguish the Direct INSERT Limit
INSERT followed directly by VALUES supports at most one thousand rows in that statement. That limit applies to the direct multi-row insert form. It isn't a universal limit on every derived VALUES relation.
As a derived table in FROM, the constructor doesn't have that same row-count limit. INSERT SELECT from that relation uses the derived form. Practical statement size and compilation work still matter.
For a large import, don't build enormous SQL text merely to avoid the direct limit. Use an appropriate bulk or parameterized loading design. A syntactic workaround isn't automatically a good data-transfer method.
The following sample shows both supported forms with a small list. It doesn't need one thousand rows to demonstrate the distinction. The destination column names remain explicit.
A table constructor is useful because its rows are visible in the query. Thousands of pasted rows reduce that visibility. The right abstraction changes as the list grows.
CREATE TABLE #ConstructorLoadDemo(ItemId int PRIMARY KEY, LabelText varchar(20) NOT NULL);
INSERT #ConstructorLoadDemo(ItemId, LabelText)
VALUES(1, 'First'), (2, 'Second');
INSERT #ConstructorLoadDemo(ItemId, LabelText)
SELECT ItemId, LabelText FROM
(VALUES(3, 'Third'), (4, 'Fourth')) AS v(ItemId, LabelText);
SELECT ItemId, LabelText FROM #ConstructorLoadDemo ORDER BY ItemId;Make Missing Codes and Duplicates Visible
A mapping list has the same quality risks as a permanent table. Two labels for one code create ambiguity. A missing code creates an uncovered case.
The next query checks duplicates within a deliberately problematic list. It gives the rule a visible failure condition. Use the same check when generated application input supplies the list.
VALUES preserves duplicate rows. It doesn't apply DISTINCT automatically. That behavior is useful when duplicates are meaningful and dangerous when you assume uniqueness.
If the query needs one mapping row per code, enforce that in a temporary or permanent table for larger inputs. A primary key then rejects duplicates. An inline list requires an explicit review or validation rule.
What should happen when an unseen status reaches this query? Decide that before adding COALESCE to every output. A warning, rejection or preserved unknown row are different contracts.
SELECT StatusCode, COUNT_BIG(*) AS DuplicateRows
FROM (VALUES(1, 'Pending'), (1, 'Waiting'), (2, 'Approved')) AS m(StatusCode, StatusName)
GROUP BY StatusCode HAVING COUNT_BIG(*) > 1;Move Reusable Lists Into a Temporary Table
A temporary table supports indexes, constraints and statistics for larger intermediate inputs. It also allows reuse across several statements on the same connection. Those benefits can justify a separate load step.
A permanent lookup is appropriate when the mapping is shared business data. Changes then belong in a controlled update process. Repeating the same mapping in several queries invites drift.
Inline VALUES remains attractive for a small, query-specific list. The optimizer can see those supplied expressions. It doesn't need a permanent object to represent every short relation.
I compare complete query work before replacing a larger list. Compilation time belongs in that test as well as execution. A shorter data transfer can still produce a larger compiled statement.
The table constructor keeps small relations readable and local. Keep its types, uniqueness and missing-code behavior explicit. Four reference cards don't need a warehouse, but a warehouse shouldn't live in one query window.
Related reading on this blog: Insert Multiple Values into Multiple Tables in a Single Statement: SQL in Sixty Seconds #132 and Beginning Table Valued Constructors: Notes from the Field #052.
An inline list is not unstructured text, it is a relation with types and join behavior.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
