Denham Coder

There are occasions when testing TSQL that we only need a small amount of input data and do not want to go through the hassle of creating, populating and then deleting tables. A nice quick solution is generating dynamic data through a Common Table Expression (CTE).

Take a look at the following code.

WITH CTE
AS
(
   SELECT *
   FROM   ( VALUES
             ( CAST(1 AS NUMERIC(5,2)), CAST(4 AS NUMERIC(5,2)) ),
             ( CAST(2 AS NUMERIC(5,2)), CAST(5 AS NUMERIC(5,2)) ),
             ( CAST(3 AS NUMERIC(5,2)), CAST(6 AS NUMERIC(5,2)) )
          ) AS D (Col1, Col2)
)
SELECT *
FROM   CTE;

TSQL allows us to create a Derived Table which is a …FROM (query)… statement which includes a query. These were very popular before CTEs became available. Starting in SQL 2008, Table Value Constructors were introduced which allowed us to create a list of row level expressions. In the example above I created 3 rows with 2 columns called Col1 and Col2. It is worth casting your data to ensure it meets your requirements however; for most cases casting can be ignored. Lastly we can select from the CTE to carry out anything we desire.

You will see me using this format to demonstrate other TSQL Tips.