Using VALUES to construct an in-line table.

1

May 22, 2019 by Kenneth Fisher

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)

One thought on “Using VALUES to construct an in-line table.

  1. […] Kenneth Fisher shows how to use the VALUES clause to construct a virtual table: […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,506 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: