Create On-The-Fly SQL Tables
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:
<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:
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.
Leave a Reply
Want to join the discussion?Feel free to contribute!