When you need to test queries, stored procedures, or reporting logic, having quick access to realistic data is imperative. While tools like INSERT...SELECT from other tables or importing CSV files are common, sometimes you just want to spin up a set of rows on the fly without messing with permanent tables. That’s where the VALUES clause can help.
The VALUES clause is usually seen in simple INSERT statements, but it can also be used directly in a SELECT statement to create inline datasets. This can be especially handy for mocking up scenarios, testing joins, or building quick prototypes.
Example
Suppose you’re testing some reporting logic around software license usage for a set of companies. Instead of creating and populating a table, you can just declare the dataset inline with the VALUES clause:
SELECT *
FROM (VALUES
(101, 'Redwood Robotics', 'Enterprise', 850, '2025-02-14'),
(102, 'Ironclad Brewery', 'Pro', 120, '2025-01-10'),
(103, 'Neon Transit', 'Basic', 45, '2025-12-05'),
(104, 'Solaris BioTech', 'Enterprise', 1300, '2025-10-22'),
(105, 'Blackwater Instruments','Pro', 275, '2025-11-18'),
(106, 'Echo Forge Studios', 'Basic', 60, '2026-01-29'),
(107, 'Glacier Outfitters', 'Pro', 190, '2026-03-30'),
(108, 'Zephyr Aerospace', 'Enterprise', 2200, '2025-02-05')
) AS CompanyData(CompanyID, CompanyName, LicenseTier, ActiveUsers, RenewalDate);Output:
CompanyID CompanyName LicenseTier ActiveUsers RenewalDate
--------- ---------------------- ----------- ----------- -----------
101 Redwood Robotics Enterprise 850 2025-02-14
102 Ironclad Brewery Pro 120 2025-01-10
103 Neon Transit Basic 45 2025-12-05
104 Solaris BioTech Enterprise 1300 2025-10-22
105 Blackwater Instruments Pro 275 2025-11-18
106 Echo Forge Studios Basic 60 2026-01-29
107 Glacier Outfitters Pro 190 2026-03-30
108 Zephyr Aerospace Enterprise 2200 2025-02-05
So we can see that the code snippet generated a table-like result set immediately. Each row represents a company with details about its license tier, active users, and renewal date. The column aliases (CompanyID, CompanyName, LicenseTier, ActiveUsers, RenewalDate) make it behave just like a regular table. You can now write queries against it as if it were real data.
For instance, you can filter for customers with renewals coming up soon:
SELECT
CompanyName,
LicenseTier,
GETDATE() AS CurrentDate,
RenewalDate
FROM (VALUES
(101, 'Redwood Robotics', 'Enterprise', 850, '2025-02-14'),
(102, 'Ironclad Brewery', 'Pro', 120, '2025-01-10'),
(103, 'Neon Transit', 'Basic', 45, '2025-12-05'),
(104, 'Solaris BioTech', 'Enterprise', 1300, '2025-10-22'),
(105, 'Blackwater Instruments','Pro', 275, '2025-11-18'),
(106, 'Echo Forge Studios', 'Basic', 60, '2026-01-29'),
(107, 'Glacier Outfitters', 'Pro', 190, '2026-03-30'),
(108, 'Zephyr Aerospace', 'Enterprise', 2200, '2025-02-05')
) AS CompanyData(CompanyID, CompanyName, LicenseTier, ActiveUsers, RenewalDate)
WHERE RenewalDate
BETWEEN GETDATE()
AND DATEADD(DAY, 60, GETDATE());Output:
CompanyName LicenseTier CurrentDate RenewalDate
---------------------- ----------- ------------------------ -----------
Solaris BioTech Enterprise 2025-09-27T00:33:11.783Z 2025-10-22
Blackwater Instruments Pro 2025-09-27T00:33:11.783Z 2025-11-18
It’s basically like filtering the rows in a table.
Joining VALUES Clauses
We can use SQL joins against multiple VALUES clauses. For example:
WITH Companies AS (
SELECT CompanyID, CompanyName, LicenseTier, ActiveUsers, CAST(RenewalDate AS date) AS RenewalDate
FROM (VALUES
(101, 'Redwood Robotics', 'Enterprise', 850, '2025-02-14'),
(102, 'Ironclad Brewery', 'Pro', 120, '2025-01-10'),
(103, 'Neon Transit', 'Basic', 45, '2025-12-05'),
(104, 'Solaris BioTech', 'Enterprise', 1300, '2025-10-22'),
(105, 'Blackwater Instruments', 'Pro', 275, '2025-11-18'),
(106, 'Echo Forge Studios', 'Basic', 60, '2026-01-29'),
(107, 'Glacier Outfitters', 'Pro', 190, '2026-03-30'),
(108, 'Zephyr Aerospace', 'Enterprise', 2200, '2025-02-05')
) AS v(CompanyID, CompanyName, LicenseTier, ActiveUsers, RenewalDate)
),
Invoices AS (
SELECT InvoiceID, CompanyID, Amount, CAST(InvoiceDate AS date) AS InvoiceDate
FROM (VALUES
(5001, 101, 1250.00, '2025-01-20'),
(5002, 101, 1875.00, '2025-02-15'),
(5003, 102, 600.00, '2025-01-05'),
(5004, 103, 250.00, '2025-12-10'),
(5005, 104, 9900.00, '2025-10-25'),
(5006, 105, 1450.00, '2025-11-01'),
(5007, 105, 800.00, '2025-11-20'),
(5008, 108, 22000.00, '2025-02-10')
) AS v(InvoiceID, CompanyID, Amount, InvoiceDate)
)
SELECT
c.CompanyName,
c.LicenseTier,
SUM(i.Amount) AS TotalInvoiced,
COUNT(i.InvoiceID) AS InvoiceCount,
MIN(i.InvoiceDate) AS FirstInvoice,
MAX(i.InvoiceDate) AS LastInvoice
FROM Companies c
LEFT JOIN Invoices i
ON c.CompanyID = i.CompanyID
GROUP BY c.CompanyName, c.LicenseTier
ORDER BY TotalInvoiced DESC;Result:
CompanyName LicenseTier TotalInvoiced InvoiceCount FirstInvoice LastInvoice
---------------------- ----------- ------------- ------------ ------------------------ ------------------------
Zephyr Aerospace Enterprise 22000 1 2025-02-10T00:00:00.000Z 2025-02-10T00:00:00.000Z
Solaris BioTech Enterprise 9900 1 2025-10-25T00:00:00.000Z 2025-10-25T00:00:00.000Z
Redwood Robotics Enterprise 3125 2 2025-01-20T00:00:00.000Z 2025-02-15T00:00:00.000Z
Blackwater Instruments Pro 2250 2 2025-11-01T00:00:00.000Z 2025-11-20T00:00:00.000Z
Ironclad Brewery Pro 600 1 2025-01-05T00:00:00.000Z 2025-01-05T00:00:00.000Z
Neon Transit Basic 250 1 2025-12-10T00:00:00.000Z 2025-12-10T00:00:00.000Z
Echo Forge Studios Basic null 0 null null
Glacier Outfitters Pro null 0 null null
Again, it’s just like joining two tables in a query. We were able to use data in both VALUES clauses to provide a combined result, based on related data.
Benefits
Here are some of the benefits of using the VALUES clause for test data:
- Speed: You don’t need to create temp tables or permanent tables for quick scenarios.
- Portability: The dataset lives in the query, so you can copy-paste it into test scripts, share it in emails, or drop it into documentation.
- Flexibility: Since it behaves like a table, you can join it to other tables, aggregate it, or filter it.
Use Cases
The VALUES clause can be useful in cases like:
- Testing stored procedure logic without needing a populated table.
- Demonstrating examples in documentation or training sessions.
- Quickly mocking data for ad-hoc analysis.
It’s not a replacement for proper test datasets, but it’s a nice little trick for when you just need something lightweight, fast, and disposable.