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

SQL SERVER - Creating Dataset Using VALUES Clause Without Creating A Table multirow1

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

SQL SERVER - Creating Dataset Using VALUES Clause Without Creating A Table multirow2

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)

Previous Post
Interview Question of the Week #027 – Move TempDB from One Drive to Another Drive When Low Disk Space
Next Post
SQL SERVER – Identifying Blocking Chain Using SQL Scripts

Related Posts

No results found.

22 Comments. Leave new

  • That’s another cool way of adding values to a table only less columns and records are there . good example

    Reply
  • Great trick. Thank You sir.

    Reply
  • Can i do aggregate functions with this table.

    Reply
  • Ya..Its Great ..working.

    Reply
  • vivekjamshedpur
    July 7, 2015 12:43 am

    Nice!! I like this trick. Thank you.

    Reply
  • Here is the UNPIVOT trick using VALUES clause

    Reply
  • 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

    Reply
  • Chris Fischer
    July 12, 2015 8:16 am

    This technique is particularly helpful when used in a CROSS APPLY where the values are columns from the driving query.

    Reply
    • Yes that’s absolutely correct.

      Reply
    • Chris Fischer
      July 14, 2015 5:27 pm

      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.

      Reply
  • 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

    Reply
  • Mubashar Anwer
    August 3, 2015 7:17 pm

    V tricky like it Sir M really Enjoying It

    Reply
  • John Lovely
    May 7, 2019 5:29 pm

    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?

    Reply
  • ‘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

    Reply
  • ;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

    Reply
  • 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!

    Reply

Leave a Reply

Menu