WITH Syntax to Create a Temporary Table

No comments

The WITH (common_table_expression) command creates a temporary table that can be used in a SELECT, INSERT, UPDATE, MERGE, or DELETE statement. Very useful for creating temporary tables to simplify complex search statements.

USE msdb;
GO

WITH MSDBTable (JobID, JobName, LogType)
AS
-- Define the CTE query.
(
SELECT job_id, name, notify_level_eventlog
FROM dbo.sysjobs
WHERE name IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT JobID, JobName, LogType
FROM MSDBTable
ORDER BY JobName;
GO

This was a VERY simple example, but you can see how WITH can be expanded to handle much more complex statements.

Here is a terrific use of WITH to update a column using another column within a table. This was used to repopulate a column with data so I could apply a unique constraint. Note the use of OVER (PARTITION BY …) to aggregate the ROW_NUMBER:

WITH T(ColumnToUpdate, LineNum) AS
(
SELECT ColumnToUpdate, ROW_NUMBER() OVER (PARTITION BY AnotherColumnName ORDER BY ColumnToUpdate) AS LineNum FROM TableName
)
UPDATE P
SET P.ColumnToUpdate = T.LineNum
FROM TableName AS P
JOIN T ON p.ColumnToUpdate = t.ColumnToUpdate

Leave a Reply