Temp table not working in Pipeline Script activities

Thankyou, nilendraFabric, for your response.

Hi DominilMs,

We appreciate your question on the Microsoft Fabric Community Forum.

From what I understand, @Temp TABLE refers to table variables, which are limited to the current batch or block of the script. This means they work only inside a single Script activity if all operations happen within that same script. On the other hand, #Temp TABLE are temporary tables that only exist for the current SQL session.

In Microsoft Fabric Pipelines, every Script activity runs in its own separate session. Because of this, the #Temp table created in one activity or statement is not accessible in the next activity, even if it is inside the same pipeline. This is why you see the error: 'Invalid object name '#temp'.'

To keep data available across different activities or to avoid this problem, please follow the steps below:

1. Use permanent tables by creating a normal table in your Lakehouse or Warehouse for temporary data. Permanent tables stay available across sessions, so you will not face the scope problem.
   DROP TABLE IF EXISTS dbo.TempStorage;
   CREATE TABLE dbo.TempStorage (sCode VARCHAR(30));
   INSERT INTO dbo.TempStorage (sCode) VALUES ('1');
   SELECT * FROM dbo.TempStorage;
2. If you prefer to use temporary structures like @Temp, make sure all related operations (creation, insert, and select) happen inside one script only. This works because @Temp is available only during the execution of one activity.
   DECLARE @Temp TABLE (sCode VARCHAR(30));
   INSERT INTO @Temp (sCode) VALUES ('1');
   SELECT * FROM @Temp;

If you find this response helpful, please mark it as the accepted solution and give kudos. This will help other community members facing the same problem.

If you have any more questions, please feel free to ask the Microsoft Fabric community.

Thank you.