can we create temporary table in ADF

Question

I want to create temporary table using ADF pipeline and use that temporary table in another pipeline.

Please suggest any option to create temporary table?

Answer 1

Hello Bommisetty, Rakesh (Hudson IT Consultant),

Welcome to the MS Q&A platform.

Below are similar threads discussed on this topic.

https://stackoverflow.com/questions/56119601/how-to-create-temp-tables-in-sql-to-be-used-in-several-adf-activities

https://learn.microsoft.com/en-us/answers/questions/129102/temporary-table-query-error-in-copy-data-in-adf

Using ADF, creating and using the physical table, and dropping it later is recommended.

ADF won’t keep the connection between two activities if you use a real table instead of a temporary one. Then you will get the expected result. The suggestion is not to use a temporary table in ADF if the data need more than one activity to access.

This happens because the session is dropped when a pipeline activity ends, which also causes the temporary table to 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.

Copy activity doesn't natively support loading data into a database temporary table. But you can create a Stored Procedure with the Global Temporary table and use the SP on the copy activity. However, this will work only as long as the session that creates the temp table is active.

We have the below product feedback items, which would be open for the user community to upvote & comment on. This allows product teams to prioritize your idea against our existing feature backlog effectively and gives insight into the potential impact of implementing the suggested feature. Please provide your feedback on these suggested features.

https://feedback.azure.com/d365community/idea/672e005d-7226-ec11-b6e6-000d3a4f032c

https://feedback.azure.com/d365community/idea/9e8a2ae1-7026-ec11-b6e6-000d3a4f032c

I hope this helps. Please let me know if you have any further questions.