将执行动态查询的结果插入到表中 [英] Insert result of executing dynamic query into a table

问题描述

我有一个动态查询 @strQuery，它在执行时给出了包含很多列的结果.我想将此动态查询的结果插入到临时表中.我这样做是因为我想对临时表执行一些过滤并获得所需的结果.

I have a dynamic query @strQuery which on executing gives a result with lots of column. I want to insert the result from this dynamic query into a temporary table . I am doing this because I want to perform some filtering on the temporary table and get required result .

在之前的帖子中提出了类似的问题 HERE其中先创建一个临时表，然后使用INSERT INTO插入数据.

A similar question was asked on previous thread HERE in which a temporary table is created first and then data inserted using INSERT INTO.

我想避免这一步，因为列列表很长，而且我不知道字段的数据类型.

I want to avoid this step due to long list of columns and also the datatypes of fields is not known to me.

select * into #tmh from
exec(@strQuery)

错误信息

关键字exec"附近的语法不正确.

如何做到这一点?有可能以这种方式完成吗?如果没有，请指定一些其他替代方法来将执行动态查询的结果存储到表中.谢谢.

How to do this ? Is it possible to be done in this way ? If not , please specify some other alternative to get store the result on executing dynamic query into a table. Thanks.

推荐答案

我以前也遇到过这种情况，我是这样做的:

I have faced this situation before and here is what I did:

DECLARE @strQuery nVarchar(100)

SET @strQuery='SELECT * into [tempdb].[dbo].[temptable] FROM YourTable'

EXECUTE sp_executesql @strQuery

SELECT * FROM [tempdb].[dbo].[temptable]

DROP TABLE [tempdb].[dbo].[temptable]

它工作正常.不要问我为什么是 FQ 表名而不是 #temptable.我不知道.这是行不通的.我让它工作的唯一方法是使用 [tempdb].[dbo].[temptable]

It works fine. Don't ask me why a FQ table name and not #temptable. I have no idea. It does not work. The only way I could get it working was using [tempdb].[dbo].[temptable]

这篇关于将执行动态查询的结果插入到表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！