Temporary table query error in COPY DATA in ADF

Question

HI Tam,
I am using COPY DATA activity in AZURE DATA FACTORY

Below is my source

CREATE TABLE #T (COUNTRY VARCHAR(3),CODE bigint)

INSERT INTO #T(COUNTRY,CODE) VALUES ('INDIA',1000000001);  
INSERT INTO #T(COUNTRY,CODE) VALUES ('AMERICA',1000000011);  
INSERT INTO #T(COUNTRY,CODE) VALUES ('SOUTH AFRICA',1000000015);  
INSERT INTO #T(COUNTRY,CODE) VALUES ('SRILANK',1000000012);  

  

SELECT * FROM #T
LEFT JOIN
(
SELECT DISTINCT CODE,NAME,SALARY,ID FROM EMPLOYEE_TABLE E
INNER JOIN MAM M
ON M.ID=E.ID
)P
ON P.CODE=#T.CODE

DROP TABLE #T

WHEN i am run the my query in SSMS its working Good
When using QUery in Source and mapping error below like

Answer 1

Hi @Shanvitha ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

As @Vaibhav Chaudhari mentioned, using physical tables is better approach. Documentation suggests the same about temporary tables used for loading data and it applies for reading as well.

Copy activity currently doesn't natively support loading data into a database temporary table.

Workaround of achieving this can be by using Stored Procedures, Global Temporary tables (##T) and the account created should be admin on the SQL. Please create a stored procedure to read data from temp table as below (For testing did not use join query you mentioned above but it will work for that as well). Data factory can read the data from this stored procedure as in below GIF. However, this will work only as long as the session that creates the temp table is active.

create proc testtemp as  
begin  
select * from ##T  
end  

Currently ADF does not retain sessions between activities, but there is another workaround to create global temporary table from ADF and retain session until the data is loaded and used from temporary table in subsequent activities as mentioned in this GitHub link.

Please use below references for the additional details on the same - GitHub issue, StackOverflow issue

Below are the feedback items related to this requirement which can be upvoted as this enhances the user experience to use temp tables from ADF in future. All of the feedback you share in these forums will be monitored and reviewed by the Microsoft engineering teams responsible for building Azure.

38287108-persist-global-temporary-tables-between-activities
34617748-post-copy-script-in-copy-activity

Hope this helps! Please let us know for further queries and we will be glad to assist.

-------------------------------------------------------------------------------------

• Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.
• Want a reminder to come back and check responses? Here is how to subscribe to a notification.

Answer 2

Instead of temp table, you can try to create and use the physical table like dbo.tempT table and drop it later

Otherwise try a query with a table variable like below in Copy data - Source query editor -

DECLARE @T TABLE (COUNTRY VARCHAR(30), CODE BIGINT)

INSERT INTO @T (COUNTRY, CODE)
VALUES ('INDIA', 1000000001);

INSERT INTO @T (COUNTRY, CODE)
VALUES ('AMERICA', 1000000011);

INSERT INTO @T (COUNTRY, CODE)
VALUES ('SOUTH AFRICA', 1000000015);

INSERT INTO @T (COUNTRY, CODE)
VALUES ('SRILANK', 1000000012);

SELECT *
FROM @T
LEFT JOIN (
SELECT DISTINCT CODE, NAME, SALARY, ID
FROM EMPLOYEE_TABLE E
INNER JOIN MAM M ON M.ID = E.ID
) P ON P.CODE = @T.CODE

---

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav