将存储过程的结果插入临时表 | 将存储过程的结果插入临时表

本文介绍了将存储过程的结果插入临时表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

如何执行 SELECT * INTO [temp table] FROM [stored procedure]?不是 FROM [Table] 并且没有定义 [temp table]?

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]?

Select 所有数据从 BusinessLine 到 tmpBusLine 工作正常.

Select all data from BusinessLine into tmpBusLine works fine.

select *
into tmpBusLine
from BusinessLine

我正在尝试相同的方法，但使用返回数据的 存储过程 并不完全相同.

I am trying the same, but using a stored procedure that returns data, is not quite the same.

select *
into tmpBusLine
from
exec getBusinessLineHistory '16 Mar 2009'

输出消息:

消息 156，级别 15，状态 1，第 2 行关键字附近的语法不正确'执行'.

我已经阅读了几个创建与输出存储过程具有相同结构的临时表的示例，它们工作正常，但最好不提供任何列.

I have read several examples of creating a temporary table with the same structure as the output stored procedure, which works fine, but it would be nice to not supply any columns.

推荐答案

您可以使用 OPENROWSET 为此.看一看.我还包含了 sp_configure 代码以启用 Ad Hoc 分布式查询，以防它尚未启用.

You can use OPENROWSET for this. Have a look. I've also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn't already enabled.

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

这篇关于将存储过程的结果插入临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！