MS SQL Server CTEs And Temporary Tables Not Playing Nice? Reverse Things.

The Problem

I had a MS SQL Server query that used a Common Table Expression (“CTE”) and a temporary table, and was getting the following error:

Incorrect syntax near ';'.

I thought I had to clearly separate the statements for creating the CTE and the temporary table.

My query followed this format:

-- This does NOT work
WITH cteTable AS (
    SELECT field1, field2 FROM myTable
  UNION ALL
    SELECT field2, field2 FROM myTable2
);

DROP TABLE IF EXISTS #tempValid;

CREATE TABLE #tempValid (
  field1 VARCHAR(2)
);

INSERT INTO #tempValid (field1) VALUES
  ('a'),
  ('b'),
  ('c');

SELECT field1, field2
FROM someOtherTable A
LEFT JOIN #tempValid B ON
  A.field1 = B.field1
LEFT JOIN cteTable C ON
  A.field2 = C.field2

This is the exact phrase I used when Googling:

“SQL Server error with CTE and creating a table in the same query”

And the first result I clicked on did not give me the answer I hoped for. So here it is.

The Solution

Just change the order. Apparently in MS SQL Server, you can’t create a temporary table before defining the CTE

This worked:

-- Create temporary table before the CTE
DROP TABLE IF EXISTS #tempValid;

CREATE TABLE #tempValid (
  field1 VARCHAR(2)
);

INSERT INTO #tempValid (field1) VALUES
('a'),
('b'),
('c');

WITH cteTable AS (
    SELECT field1, field2 FROM myTable
  UNION ALL
    SELECT field2, field2 FROM myTable2
)


SELECT field1, field2
FROM someOtherTable A
LEFT JOIN #tempValid B ON
  A.field1 = B.field1
LEFT JOIN cteTable C ON
  A.field2 = C.field2