SQL SERVER – Creating Dataset Using VALUES Clause Without Creating A Table

You know that you can add data to a table using VALUES clause. But did you know that you can create a dataset using VALUES clause like a table without adding into another table?

Suppose you want to create a data set with two columns named a and b

SELECT *
FROM (VALUES (1,2)) AS t(a,b)


If you execute the above query, you get the following resultset

If you want multiple rows, you can add data seperated by comma as shown below

SELECT *
FROM (VALUES (1,2),(3,4),(340,455)) AS t(a,b)


The result is

This is very handy if you want to have a small lookup table that can be matched with another table

Please note that this method of using VALUES clause will work starting from version 2008 onwards