Microsoft SQL Server：运行任意查询并将结果保存到临时表中

发布于 2025-02-08 20:36:44 字数 1657 浏览 1 评论 0原文

给定一个任意 选择查询，如何将其结果保存到临时表中？

为了简化事物，让我们假设选择查询不包含从顶级子句中的顺序；这不是动态的SQL；它确实是选择（不是存储过程调用），并且是一个查询（不是返回多个结果集的东西）。所有列都有一个明确的名称。如何运行它并将结果保存到临时表？通过处理客户端的SQL，或者通过T-SQL中的一些聪明的东西。

我不是在询问任何特定的查询 - 显然，考虑到某些特定的SQL，我可以手动将其重写以节省临时表 - 而是关于一般可以使用并且可以编程的规则。

一个可能的“答案”一般不起作用

您可以做的简单查询 从（UNDL）x中选择 * to #tmp undl 是基础SQL查询。但是，如果undl是一个更复杂的查询，这将失败；例如，如果它使用使用使用通用表表达式。

出于类似的原因，<代码> x as（utl）从x 中select * select *在#tmp中通常不起作用； <代码>带有条款无法嵌套。

我当前的方法，但不容易编程

我发现的最好的方法是找到QUERY的顶级并将其添加到#TMP中在关键字的之前。但是找到哪个选择对Munge并不容易；它需要在一般情况下解析整个查询。

可能具有用户定义函数的解决方案

一种方法可能是创建用户定义的函数包装查询，然后select *从dbo.my_function（） select * to #tmp中之后的功能。有更好的东西吗？

更多详细信息，详细说明了为什么在基础使用CTE时简单方法失败。假设我尝试从（UNDL）x 中的规则选择 * in #tmp中，其中undl是基础SQL。现在，让UNDL用mycte AS（选择5 as Mycol）从mycte中选择mycol。应用规则后，最终查询为select *从（从mycte as（select 5 as mycol）中）从mycte中选择mycol）x x ，它不是有效的SQL，至少在我的版本（MSSQL 2016）。无法嵌套条款。
要清楚，必须在选择之前在顶级定义CTE。它们不能嵌套，也不能出现在子征服中。我完全了解这一点，这就是为什么我问这个问题的原因。试图包装最终试图嵌套CTE的SQL无法使用的SQL。我正在寻找一种可以起作用的方法。
“在选择之前，将放入中”。这肯定会起作用，但需要在一般情况下解析SQL。（对于计算机程序）并不总是显而易见的，选择需要更改。我确实尝试了将其添加到查询中的最后一个选择的规则，但这也失败了。例如，如果基础查询为

a mycte as（选择5 as Mycol）从mycte中选择mycol，则选择6

，则需要将添加到#x中>第二个选择，而不是以外出现的之后出现的选择。在一般情况下，将其正确处理涉及将SQL解析到语法树中。

原文

Given an arbitrary select query, how can I save its results into a temporary table?

To simplify things let's assume the select query does not contain an order by clause at the top level; it's not dynamic SQL; it really is a select (not a stored procedure call), and it's a single query (not something that returns multiple result sets). All of the columns have an explicit name. How can I run it and save the results to a temp table? Either by processing the SQL on the client side, or by something clever in T-SQL.

I am not asking about any particular query -- obviously, given some particular SQL I could rewrite it by hand to save into a temp table -- but about a rule that will work in general and can be programmed.

One possible "answer" that does not work in general

For simple queries you can do
select * into #tmp from (undl) x where undl is the underlying SQL query. But this fails if undl is a more complex query; for example if it uses common table expressions using with.

For similar reasons with x as (undl) select * into #tmp from x does not work in general; with clauses cannot be nested.

My current approach, but not easy to program

The best I've found is to find the top level select of the query and munge it to add into #tmp just before the from keyword. But finding which select to munge is not easy; it requires parsing the whole query in the general case.

Possible solution with user-defined function

One approach may be to create a user-defined function wrapping the query, then select * into #tmp from dbo.my_function() and drop the function afterwards. Is there something better?

More detail on why the simple approach fails when the underlying uses CTEs. Suppose I try the rule select * into #tmp from (undl) x where undl is the underlying SQL. Now let undl be with mycte as (select 5 as mycol) select mycol from mycte. Once the rule is applied, the final query is select * into #tmp from (with mycte as (select 5 as mycol) select mycol from mycte) x which is not valid SQL, at least not on my version (MSSQL 2016). with clauses cannot be nested.
To be clear, CTEs must be defined at the top level before the select. They cannot be nested and cannot appear in subqueries. I fully understand that and it's why I am asking this question. An attempt to wrap the SQL that ends up trying to nest the CTEs will not work. I am looking for an approach that will work.
"Put an into right before the select". This will certainly work but requires parsing the SQL in the general case. It's not always obvious (to a computer program) which select needs to change. I did try the rule of adding it to the last select in the query, but this also fails. For example if the underlying query is

with mycte as (select 5 as mycol) select mycol from mycte except select 6

then the into #x needs to be added to the second select, not to the one that appears after except. Getting this right in the general case involves parsing the SQL into a syntax tree.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情话墙 2025-02-15 20:36:44

最后，创建用户定义的功能似乎是唯一的一般答案。如果undl是基础选择查询，则可以说

create function dbo.myfunc() returns table as return (undl)
go
select * into #tmp from dbo.myfunc()
go
drop function dbo.myfunc
go

pseudo-sql go指示启动新批次。 创建函数必须在选择之前一批执行，否则您会得到语法错误。（只需用;将它们分开是不够的。）

即使undl包含的常见表表达式，此方法也有效。但是，当查询使用临时表时，它不起作用。

In the end creating a user-defined function appears to be the only general answer. If undl is the underlying select query, then you can say

create function dbo.myfunc() returns table as return (undl)
go
select * into #tmp from dbo.myfunc()
go
drop function dbo.myfunc
go

The pseudo-SQL go indicates starting a new batch. The create function must be executed in one batch before the select, otherwise you get a syntax error. (Just separating them with ; is not enough.)

This approach works even when undl contains subqueries or common table expressions using with. However, it does not work when the query uses temporary tables.

回复收藏 0 原文

~没有更多了~