Create On-The-Fly SQL Tables

24 Jan
January 24, 2012

In this post I will show you a quick example of how to write an on-the-fly table in SQL Server.

Knowing how to quickly write an adhoc on-the-fly SQL table can come in handy mostly when performing administration tasks (such as adhoc queries), or to create a customized small tally-tables, which are used to aid the processing of your query.

There are many ways to building an on-the-fly table in SQL Server, you could essentially construct it by appending rows (using UNION) until the table is formed, but that is not very nice in terms of readability and maintainability.

SQL Server has a syntax for creating on the fly tables (which I constantly forget and have to look up), and this looks like this:

SELECT
    <temp-table-name>.<temp-col-name>,
    <temp-table-name>.<temp-col-name2>
FROM
    (
    VALUES
        (Val1,Val2),
        (Val3,Val4)
    ) AS <temp-table-name>(<temp-col-name>,<temp-col-name-2>)

Essentially using the SQL Server VALUES clause, one can build a SQL table on the fly that can be used to drive the query you are trying to perform.

Very easy.

As an example, the following query returns a table containing a bunch of Ids that are being created on the fly:

SELECT
Col1
FROM (VALUES
        (104),
        (157),
        (160),
        (161),
        (162),
        (163),
        (164)
    ) AS t(Col1)

This will return the following table:

Hope this helps. Happy SQL-ing.

* * * * ½ 2 votes
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>