SQL 将执行动态查询的结果插入到表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13984918/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert result of executing dynamic query into a table
提问by Mudassir Hasan
I have a dynamic query @strQuerywhich 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 .
我有一个动态查询@strQuery，它在执行时给出了很多列的结果。我想将此动态查询的结果插入到临时表中。我这样做是因为我想对临时表执行一些过滤并获得所需的结果。
A similar question was asked on previous thread HEREin which a temporary table is created first and then data inserted using INSERT INTO.
在上一个线程HERE上提出了一个类似的问题
，其中首先创建一个临时表，然后使用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)
Error Message
错误信息
Incorrect syntax near the keyword 'exec'.
关键字“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.
这该怎么做 ？有可能以这种方式完成吗？如果没有，请指定一些其他替代方法来将执行动态查询的结果存储到表中。谢谢。
回答by Raj
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]
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]
它工作正常。不要问我为什么是 FQ 表名而不是 #temptable。我不知道。这是行不通的。我让它工作的唯一方法是使用 [tempdb].[dbo].[temptable]
回答by Nipun Jain
proceed like this
像这样进行
select t1.name,t1.lastname from(select * from table)t1.
where "select * from table" is your dyanmic query. which will return result which you can use as temp table t1 as given in example .
其中“select * from table”是您的动态查询。这将返回结果，您可以将其用作示例中给出的临时表 t1。
回答by Richard Vivian
You can use variables in your current execution context, set by the Dynamic SQL with the OUTPUT option. Sample code below.
您可以在当前执行上下文中使用变量，由带有 OUTPUT 选项的动态 SQL 设置。下面的示例代码。
DECLARE @Amount AS MONEY
DECLARE @SQL AS NVARCHAR(1000)
SET @Amount = NULL
SET @SQL = ('SELECT @amt=100' )
EXECUTE sp_executeSQL @SQL, N'@amt MONEY OUTPUT', @amt=@Amount OUTPUT
SELECT @Amount
回答by Atheer Mostafa
Yes you can make a new dynamic query containing the original query with the insert like this:
是的，您可以创建一个包含原始查询的新动态查询，如下所示：
declare @strNewQuery varchar(max)
set @strNewQuery ='select * into #tmh from ('+@strQuery+') as t'
exec(@strNewQuery)
回答by Arun Prasad E S
I used this to work around - with out dynamic query
我用它来解决 - 没有动态查询
This uses a table variable to receive data to procedure
这使用表变量来接收数据到过程
Even joins can be applied to it
甚至可以对它应用连接
select * into #itemPhantom from @tbl_items_upload
select * from #itemPhantom
select #itemPhantom.itemreference from #itemPhantom left join phantom on phantom.name=#itemPhantom.PhantomName
