Using VALUES to construct an in-line table.

This has come up a few times recently, I find it rather fascinating and I can never seem to remember how to do it properly .. so in other words, it’s a perfect subject for a blog post.

Basically, you can use VALUES to create a table within a query. I’ve seen it done in a number of places. Mostly when demoing something and you want some data, but don’t want to actually create a table. I’ve also seen it used to create a numbers table, create test data, etc. Really, any case where you want a short list of values but don’t want to create an actual (or even temp) table to store them. Something like this:

SELECT Nums.[Values]
FROM (VALUES (1),(2),(3),(4),(5)) Nums([Values]);

You can also, of course, use it to create a multi-column table.

SELECT Data.FName, Data.LName, Data.BDate
FROM (VALUES ('Bob','Smith','1/1/2000'),
('Jane','Doe','10/4/2000'),
('Dr','Who',NULL)) Data(FName, LName, BDate);

Now, here’s where this gets interesting. That table makes some assumptions. Just because I called the 3rd column a date doesn’t mean it is one. In fact, it’s a varchar like the other two columns. That’s kind of important if you decide to do anything to that column. If possible you’ll get an implicit conversion, and sometimes you’ll just get an error.

SELECT Data.FName, Data.LName, Data.BDate+1
FROM (VALUES ('Bob','Smith','1/1/2000'),
('Jane','Doe','10/4/2000'),
('Dr','Who',NULL)) Data(FName, LName, BDate);

Msg 245, Level 16, State 1, Line 4

Conversion failed when converting the varchar value ‘1/1/2000’ to data type int.

To fix that you’ll want to do make sure that SQL knows what the datatype is actually supposed to be. There may be other ways to do this but I’ve found if you do an explicit conversion on the column in the first row that it will fix the datatype for the entire table.

SELECT Data.FName, Data.LName, Data.BDate+1
FROM (VALUES ('Bob','Smith',CAST('1/1/2000' AS DateTime)),
('Jane','Doe','10/4/2000'),
('Dr','Who',NULL)) Data(FName, LName, BDate);

These types of things can get particularly interesting if you start combining them together.

SELECT First.Name, Last.Name
FROM (VALUES ('Bob'), ('Jane'), ('Dr')) First(Name)
CROSS JOIN (VALUES ('Smith'), ('Doe'), ('Who')) Last(Name);

Don’t forget that at the end of the list of values you have to give your table along with each of the columns in it a name so you have something to call it throughout the rest of the query.

(VALUES ('col1','col2'), -- Row1
('col1','col2'), -- Row2
('col1','col2'), -- Row3 etc
) TableName(Column1Name, Column2Name)