Creating Temporary Tables in SQL

How to create Temp table with SELECT * INTO tempTable FROM CTE Query

Sample DDL

create table #Temp
(
    EventID int, 
    EventTitle Varchar(50), 
    EventStartDate DateTime, 
    EventEndDate DatetIme, 
    EventEnumDays int,
    EventStartTime Datetime,
    EventEndTime DateTime, 
    EventRecurring Bit, 
    EventType int
)

---

;WITH Calendar
AS (SELECT /*...*/)

Insert Into #Temp
Select EventID, EventStartDate, EventEndDate, PlannedDate as [EventDates], Cast(PlannedDate As datetime) AS DT, Cast(EventStartTime As time) AS ST,Cast(EventEndTime As time) AS ET, EventTitle
,EventType from Calendar
where (PlannedDate >= GETDATE()) AND ',' + EventEnumDays + ',' like '%,' + cast(datepart(dw, PlannedDate) as char(1)) + ',%'
    or EventEnumDays is null

---

Make sure that the table is deleted after use

If(OBJECT_ID('tempdb..#temp') Is Not Null)
Begin
    Drop Table #Temp
End

How to create temp table using Create statement in SQL Server?

A temporary table can have 3 kinds, the # is the most used. This is a temp table that only exists in the current session.
An equivalent of this is @, a declared table variable. This has a little less "functions" (like indexes etc) and is also only used for the current session.
The ## is one that is the same as the #, however, the scope is wider, so you can use it within the same session, within other stored procedures.

You can create a temp table in various ways:

declare @table table (id int)
create table #table (id int)
create table ##table (id int)
select * into #table from xyz

what is the difference between create temp table and create table #temp?

Figured it out, both syntax does the same work !!

Users can either use the temp keyword or a # sign right before the table name to create a temporary table (Redshift Temp Table). In other words, to create a Redshift Temp Table, simply specify the TEMPORARY keyword (or TEMP abbreviation) or # sign in your CREATE TABLE DDL statement.

syntax 1 : CREATE TABLE #table_name (column_name1 data_type1,column_name2 data_typ2);

Syntax 2: CREATE TEMPORARY TABLE table_name (column_name1 data_type1,column_name2 data_typ2);

Syntax 3: CREATE TEMP TABLE table_name (column_name1 data_type1,column_name2 data_typ2);

Supporting documents on same -->

1. https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html
2. https://hevodata.com/learn/redshift-temp-tables/#c2

How to create temp tables in SQL to be used in several ADF activities?

I have been struggling with this myself. Apparently this is by design (see quote below from Microsoft employee) and it is not possible to achieve this using Azure Data Factory even though the documentation mentions that it is possible.

That is by design. We won’t keep connection between 2 activities.
If you use a real table instead of temporary table. Then you will get the expected result.
The suggestion is don’t used temporary table in ADF if the data need more than 1 activities to access.

https://github.com/MicrosoftDocs/azure-docs/issues/35449#issuecomment-517451867

The reason this happens is the session is dropped when a pipeline activity ends, which causes the temporary table to also be dropped.

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-2017#temporary-tables

Hopefully Microsoft fixes this at some point and makes it possible to use temporary tables across activities with Azure Data Factory.

---

I have raised this as a suggestion for Azure here https://feedback.azure.com/forums/270578-data-factory/suggestions/38287108-persist-global-temporary-tables-between-activities

For anyone reading this that might want his feature please upvote that suggestion.

---

Related Topics