如何在SQL中创建临时表以在多个ADF活动中使用? [英] How to create temp tables in SQL to be used in several ADF activities?

问题描述

在执行Azure数据工厂管道时，我需要在SQL Server中创建全局临时表.该表将在几个活动中使用.

I need to create a global temp table in my SQL Server while executing an Azure Data Factory pipeline. This table will be used in several activities.

我已经尝试了几种方法，包括一种使用针对sys.sp_executesql SP和CREATE TABLE语句作为参数的存储过程"活动的方法.通过这种方法，实际上可以创建表，但是稍后会自动将其删除，我不知道为什么.

I already tried several approaches including one using the Stored Procedure activity targeting the sys.sp_executesql SP and the CREATE TABLE statement as the parameter. With this approach the table is actually created, but it's automaticaly dropped a second later, I don't understand why.

这是用于创建临时表的脚本:

This is the script used to create the temp table:

CREATE TABLE ##tempGL
(
    GLAccount NVARCHAR(15),
    GLSubAccount NVARCHAR(15)
)

那么，如何从一个Azure Data Factory Pipeline活动创建一个SQL Server临时表，该活动会一直持续到我删除它为止?

So, how can I create a SQL Server temp table from an Azure Data Factory Pipeline activity that persists until I dropped it?

推荐答案

我一直在为此苦苦挣扎.显然，这是设计使然(请参阅下面Microsoft员工的引文)，即使文档中提到有可能，也无法使用Azure Data Factory来实现.

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.

那是设计使然.我们不会在2个活动之间保持联系. 如果使用真实表而不是临时表.然后您将获得预期的结果. 如果数据需要访问多个活动，则建议不要在ADF中使用临时表.

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.

当创建临时表的会话结束并且所有其他任务都停止引用它们时，将自动删除全局临时表.任务和表之间的关联仅在单个Transact-SQL语句的生命周期内维护.这意味着在创建会话结束时，最后一个正在主动引用该表的Transact-SQL语句完成时，将删除全局临时表.

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/zh-cn/sql/t-sql/statements/create-table-transact-sql?view=sql-server-2017#temporary-tables

希望Microsoft能够在某个时候解决此问题，并使其可以在Azure Data Factory的各个活动中使用临时表.

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

我在此处 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.

这篇关于如何在SQL中创建临时表以在多个ADF活动中使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！