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:
) 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.
As an example, the following query returns a table containing a bunch of Ids that are being created on the fly:
) AS t(Col1)
This will return the following table:
Hope this helps. Happy SQL-ing.