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
Reference: Pinal Dave (https://blog.sqlauthority.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?