How does a temporary table differ from a table initiated via the WITH keyword in MS SQL?

Question

I am having issues understanding the SOURCETABLE table from this code:

CREATE PROCEDURE [ifc_tt].[SendTimeBalance]
AS
     DECLARE @endOfPreviousMonth DATE;
     DECLARE @now DATETIME= GETDATE();
     SELECT @endOfPreviousMonth = EOMONTH(GETDATE(), -1);
     WITH sourceTable
          AS (SELECT w.EmployeeId AS corporateId, 
                     w.Date AS date, 
                     w.logaValue AS flextimeHours
              FROM rpt_tt.DQC_TimeBalance AS w
              WHERE w.Date <= @endOfPreviousMonth)
          MERGE INTO ifc_tt.TimeBalance AS t
          USING sourceTable AS s
          ON t.corporateId = s.corporateId
             AND t.date = s.date
              WHEN NOT MATCHED BY TARGET
              THEN
                INSERT(corporateId, 
                       date, 
                       flextimeHours, 
                       overtimeHours, 
                       lastUpdate, 
                       Comment)
                VALUES
          (s.corporateId, 
           s.date, 
           s.flextimeHours, 
           NULL, 
           @now, 
           NULL
          )
              WHEN MATCHED
              THEN UPDATE SET 
                              t.flextimeHours = s.flextimeHours, 
                              t.lastUpdate = @now;
     RETURN 0;

Usually, when I see temp tables, they start with an @ (see @endOfPreviousMonth).

But in this case, sourcetable is used without an @. Still I read it is similar to a temp table. What exactly is the difference here and how is it in this context here?

Thank you for your help :)

WITH keyword is used to create a CTE . Refer to this link for more insight https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017 — Shikhar Arora, Jul 03 '19 at 12:05
Temp table names start with `#`. What you're referring to is a variable; table variables are a thing, but they're not used here. A common table expression (introduced with `WITH`) is neither; effectively this is another way of writing a subquery. The optimizer treats these by expanding them into the query; they are not materialized first. — Jeroen Mostert, Jul 03 '19 at 12:05

score 4 · Accepted Answer · answered Jul 03 '19 at 12:07

The tables starting with @ that you refer to (ex. @table1), are table variables. Temporary tables are the ones you define using a # (local ones to your session) or ## (global temp tables). Ex. #tempTable1, ##tempTable2.

What you have here is a Common Table Expression. It is basically a named result set, it is not persisted as a real physical table.

It exists for as long as you are running the query, containing the data you have "filled" it with in the SELECT clause.

How does a temporary table differ from a table initiated via the WITH keyword in MS SQL?

1 Answers1