CODEWITHSUNDEEP
sqlsql-server
MrMee
MrMee

Reputation: 163

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

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 :)

Upvotes: 3

Views: 72

Answers (1)

Rigerta
Rigerta

Reputation: 4039

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.

Upvotes: 4

Related Questions