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
Handy Uses of the VALUES Clause as a Table
When you use it as a derived table, remember two small rules: the whole list goes inside parentheses, and you must give it an alias with column names, like (VALUES (1, 'Red'), (2, 'Blue')) AS Colors(Id, Name). SQL Server will not run it without the column names. It is a small detail, but it trips up many people the first time.
Data types come from the values themselves. SQL Server picks one type per column across all the rows, so if one row has a number and another has text that is not a number, the query fails with a conversion error. When the exact type matters, wrap the column in a CAST in the outer SELECT.
My favorite trick with it is finding the largest value across several columns of the same row: SELECT (SELECT MAX(v) FROM (VALUES (Col1), (Col2), (Col3)) AS x(v)) FROM dbo.MyTable. The same idea works with CROSS APPLY to turn columns into rows, which is often simpler than UNPIVOT.
One more note: a plain INSERT with a list of rows accepts at most 1,000 rows. Used as a derived table in a SELECT, that limit does not apply, though for large lists a real table is still the better choice. For a lookup list you reuse in many queries, a small permanent table is also easier to maintain than copies of the same list in several scripts.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
23 Comments. Leave new
That’s another cool way of adding values to a table only less columns and records are there . good example
theonlysup – I am glad that you liked it!
Great trick. Thank You sir.
Sachin – Thanks for your comment.
Can i do aggregate functions with this table.
Yes.
Yes. You can. Here is the UNPIVOT trick using values clause
Ya..Its Great ..working.
Great. Thanks for confirming.
Nice!! I like this trick. Thank you.
Here is the UNPIVOT trick using VALUES clause
I like putting the row constructors into a VIEW, so that it becomes a table constant that cannot be inserted into, deleted from or updated
This technique is particularly helpful when used in a CROSS APPLY where the values are columns from the driving query.
Yes that’s absolutely correct.
Thought I should give an example. :) Suppose you have a table with several “complete” dates and you want the greatest of those dates returned in a query:
CREATE TABLE mytable (
name nvarchar(100) primary key,
FirstCompleteDate datetime2(3) null,
SecondCompleteDate datetime2(3) null,
ThirdCompleteDate datetime2(3) null);
SELECT name, maxdate
FROM mytable m
CROSS APPLY (Select Max(MyDate) As MaxDate From (Values (mFirstCompleteDate datetime2(3) null,
.FirstCompleteDate), (m.SecondCompleteDate), (m.ThirdCompleteDate) ) B(MyDate) ) C;
This is faster than using a CASE statement and MUCH MUCH faster than a UDF.
Thanks for the tip; looks like a great way to build in a small in-line reference table, and will be very useful when debugging a misbehaving script.
Cheers!
Mark
Mark – I am glad that you can use it in real time.
V tricky like it Sir M really Enjoying It
I have a script generated in MySQL. This has the syntax INSERT INTO .. VALUES(),().
Unfortunately, the MySQL script has generated examples where there are more than 1,000 sets of values and SQL Server is failing.
Other than manually editing the script, does anyone know of a way of increasing the 1,000 limit?
‘a,b,c’ to be a col under name zz
;WITH Split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(‘,’,’a,b,c’) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(‘,’,’a,b,c’,endpos+1) FROM Split WHERE endpos > 0
)
–LTRIM RTRIM to get rid of white space before start or after end of str
SELECT RTRIM(LTRIM(SUBSTRING(‘a,b,c’,stpos,COALESCE(NULLIF(endpos,0),LEN(‘a,b,c’)+1)-stpos))) as zz
FROM Split
;WITH Split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(‘,’,’a,b,c’) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(‘,’,’a,b,c’,endpos+1) FROM Split WHERE endpos > 0
)
–LTRIM RTRIM to get rid of white space before start or after end of str
SELECT RTRIM(LTRIM(SUBSTRING(‘a,b,c’,stpos,COALESCE(NULLIF(endpos,0),LEN(‘a,b,c’)+1)-stpos))) as zz
FROM Split
HI,
I did execute the sample query on Azure SQL and this is the result:
SELECT *
FROM (VALUES (1,2)) AS t(a,b)
Parse error at line: 2, column: 6: Incorrect syntax near ‘(‘.
Seems like Azure SQL is not taking the “VALUES” keyword the same way as SQL does.
Is there a way to do the same (without temp tables) in Azure SQL?
Thanks!
Hello @aldo – were you able to make it work on SQL Azure?