-1

I need to create a #temp table before my list of CTE:s start so that I can use it in the end to perform calculations.

This is what I have written so far:

SELECT DISTINCT

SUM(X+Y) AS Total

INTO #Summary
FROM Table

WITH CTE_START AS
(
  SELECT DISTINCT *
  FROM TableX
)

....

I have even tried creating my #Summary as a CTE and then writing INTO before FROM. It does not work.

I have looked at similar questions on:CTE&Temp Table

I have not found anything helpful. How can I create a #temp table before my WITH CTE_START begins processing?

Community
  • 1
  • 1
user3197575
  • 269
  • 6
  • 13

1 Answers1

5

You need to terminate the statement before the CTE with a semicolon, otherwise SQL Server doesn't know the WITH isn't part of the previous statement, for example part of a table hint.

For example:

SELECT DISTINCT SUM(X+Y) AS Total INTO #Summary FROM Table;

WITH CTE_START AS ( SELECT DISTINCT * FROM TableX ) 
SELECT * FROM CTE_START
Ed B
  • 785
  • 4
  • 9
  • 1
    It's a good practice to begin `CTEs` with a semicolon, i.e. `;WITH`. I thought it was worth mentioning that. – Evaldas Buinauskas Jun 25 '15 at 12:14
  • It is funny that I did not use semicolon after the AND line of code. I simply thought it would not work. Sometimes the easier way works better than trying to create additional CTE or #Temp. Thank you. Problem solved. – user3197575 Jun 25 '15 at 12:19
  • @EvaldasBuinauskas - yes, this is a good point. Personally, I find it ugly, as the semicolon really belongs to the previous statement, but this is just a matter of personal taste. – Ed B Jun 25 '15 at 12:19
  • It's a habit that's [worth getting into](https://msdn.microsoft.com/en-us/library/ms177563.aspx): "Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version." – Damien_The_Unbeliever Jun 25 '15 at 12:38
  • @Damien_The_Unbeliever it will *never* be required :) How could they break 99% of the T-SQL in existence? They still have not even removed the deprecated session options like ansi nulls. – usr Jun 25 '15 at 13:24
  • @usr - and yet they have removed `*=` joins (for any compatibility level beyond 80). It's slow progress but it may happen one day. – Damien_The_Unbeliever Jun 25 '15 at 13:28
  • In my experience, MS tend to mark something as deprecated, still support it for 2 or 3 versions, then turn it off by default in the next, and remove it in the version after. We are now seeing things that were deprecated in 2005 (version 9) disappearing: https://msdn.microsoft.com/en-us/library/ms144262%28v=sql.110%29.aspx It's a slow process, but it does happen, and if you don't prepare for it, you may live to regret it! – Ed B Jun 25 '15 at 13:42
  • All true but breaking 99% of the T-SQL in existence is not on the table. I personally see no reason to insert semicolons except when I'm forced to. I'm not aware of any advantage but it clutters the code a bit. I do the `;WITH` thing, too. – usr Jun 25 '15 at 13:58
  • 1
    @usr - I think if this is going to happen, it'll be along the lines of, announced in SQL Server 2014, not implemented until 2 versions later (2018?), then supported as a compatibility level 13 or 14 for another three versions after that, so not likely to cause a real need to change any code for another six or seven years, by which time we're in the year 2025, and we'll all be meat-slaves to the robot overlords anyway. – Ed B Jun 25 '15 at 14:55
  • @usr - "I'm not aware of any advantage" - it's gets your code closer to standard SQL that requires them. Making any future porting efforts *slightly* easier, but also training *you* to have an easier time if you're working against a different back end (with stricter rules) – Damien_The_Unbeliever Jun 25 '15 at 16:31