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


我该怎么做 SELECT * INTO [temp table] FROM [stored procedure]?不 FROM [Table] 没有定义 [temp table]

Select 来自的所有数据 BusinessLine 成 tmpBusLine 工作正常。

select *
into tmpBusLine
from BusinessLine

我正在尝试相同,但使用一个 stored procedure 返回数据的方式并不完全相同。

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

输出消息:

消息156,第15级,状态1,第2行   关键字附近的语法不正确   'EXEC'。

我已经阅读了几个创建一个与输出存储过程结构相同的临时表的例子,它工作正常,但不提供任何列会很好。


1340
2018-03-17 10:45


起源


使用SELECT * INTO [TABLE NAME],您可以知道列,因为它们是从原始表中复制的。如果我对存储过程做同样的事情,这正是我想要的。 - Ferdeen
看到 sommarskog.se/share_data.html 和我的帖子 stackoverflow.com/questions/6215672/... - Triynko
只想指出“select * into tmpBusLine”会创建一个永久表。你可能想要“select * into #tmpBusLine”。我确定原来的海报已经找到了这个,但它可能会帮助其他人找到这篇文章,因为它是目前搜索“选择临时表”的最佳结果 - ktam33
我不知道是否已经解决了这个问题,但是你得到错误的原因是因为from关键字。 - Wes Palmer
你应该使用OPENROWSET。 - Yogesh Sharma


答案:


您可以使用 OPENROWSET 为了这。看一看。我还包括sp_configure代码以启用Ad Hoc Distributed Queries,以防它尚未启用。

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

621
2017-08-04 15:27



这是正确的方法。 OPENROWSET几乎是将存储过程的结果视为表表达式的唯一方法。 - Rob Farley
插入表格似乎有点麻烦。很多配置要做。此外,当我尝试它时,我得到“消息7357,级别16,状态2,行1无法处理对象”EXEC GetPartyAnalysisData 146“。链接服务器”(null)“的OLE DB提供程序”SQLNCLI“表示该对象具有没有列或当前用户没有该对象的权限。“所以你需要设置链接服务器...... - Ferdeen
您不需要链接服务器,但是您需要正确获取连接字符串...并且还指定存储过程的完整路径,包括数据库名称和sp的所有者。 - MartW
eeeeew!对同一台服务器的引用?讨厌。肯定更像是一个黑客,而不是手动创建临时表 - Tim Abell
我同意这是一个黑客,除非你的背靠墙,否则应该避免。将sp更改为函数可能是一个更好的角度。恕我直言。 - greg


如果您想在不先声明临时表的情况下执行此操作,则可以尝试创建用户定义的函数而不是a 存储过程 并使该用户定义的函数返回一个表。或者,如果您想使用存储过程,请尝试以下方法:

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'

518
2018-03-17 14:08



我认为重点是生成模式而不必明确声明它。 - Craig
我很想知道这和@Aaron Alton上面的解决方案有什么区别。这个似乎更简单,但我不确定任何其他含义。 - funkymushroom
这将有效,但如果您在SpGetRecords存储过程中添加其他列,则会爆炸。 - Brady
每个调用堆栈只能获得一个INSERT INTO EXEC。 SpGetRecords和它调用的任何其他proc可能不会在自己的代码中使用此策略。这可能会让SpGetRecords的维护者感到惊讶。 - Matt Stephenson
这根本没有回答这个问题,我不明白为什么会如此赞成? OP明确声明“没有定义[临时表]”,你的第一行有一个创建临时表语句。 - NickG


在SQL Server 2005中,您可以使用 INSERT INTO ... EXEC 将存储过程的结果插入表中。从 MSDN的 INSERT 文件 (对于SQL Server 2000,实际上):

--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales

268
2018-03-17 10:50



这需要预先定义authors_sales。我试图避免这种情况。谢谢。 - Ferdeen
对啊。是的,我想你可能在这种情况下运气不好。 - Matt Hamilton
我也这么认为。如此有用的插入到tmp表中,但如果您需要知道从存储过程返回的数据集结构,则不太有用。谢谢你的帮助。 - Ferdeen
这里有一篇好文章 msdn.microsoft.com/en-us/library/aa175921.aspx - Rich Andrews
要使用相同的模式,您可以按如下方式创建副本:从realTable中选择top 0 *到tempTable(stackoverflow.com/a/9206463/73794) - Even Mien


这是对您问题的略微修改版本的答案。如果您可以放弃对用户定义函数使用存储过程,则可以使用内联表值用户定义函数。这本质上是一个存储过程(将采用参数),它返回一个表作为结果集;因此,将很好地与INTO声明。

这是一个很好的 快速文章 它和其他用户定义的函数。如果仍然需要存储过程,则可以使用存储过程包装内联表值用户定义函数。当存储过程从内联表值用户定义函数调用select *时,它只传递参数。

因此,例如,您有一个内联表值用户定义函数来获取特定区域的客户列表:

CREATE FUNCTION CustomersByRegion 
(  
    @RegionID int  
)
RETURNS TABLE 
AS
RETURN 
  SELECT *
  FROM customers
  WHERE RegionID = @RegionID
GO

然后,您可以调用此函数来获得结果:

SELECT * FROM CustomersbyRegion(1)

或者做一个SELECT INTO:

SELECT * INTO CustList FROM CustomersbyRegion(1)

如果仍需要存储过程,则将函数包装为:

CREATE PROCEDURE uspCustomersByRegion 
(  
    @regionID int  
)
AS
BEGIN
     SELECT * FROM CustomersbyRegion(@regionID);
END
GO

我认为这是获得预期结果的最“无懈怠”的方法。它使用现有功能,因为它们的使用目的没有其他复杂性。通过在存储过程中嵌套内联表值的用户定义函数,您可以通过两种方式访问​​该功能。加!实际的SQL代码只有一个维护点。

已经建议使用OPENROWSET,但这不是OPENROWSET函数的用途(来自联机丛书):

包括所有连接信息   这是访问远程数据所必需的   来自OLE DB数据源。这个   method是访问的替代方法   链接服务器中的表是一个   一次性,特殊的连接方法   并使用OLE访问远程数据   D B。对于更频繁的引用   OLE DB数据源,使用链接   而服务器。

使用OPENROWSET将完成工作,但是它会产生一些额外的开销,用于打开本地连接和编组数据。它也可能不是所有情况下的选项,因为它需要临时查询许可,这会带来安全风险,因此可能不是所希望的。此外,OPENROWSET方法将排除使用返回多个结果集的存储过程。在单个存储过程中包装多个内联表值用户定义函数可以实现此目的。


167
2017-08-04 17:11



+1表值函数是一个合适的解决方案。我们应该注意一些小缺点:表值函数是一个额外的数据库对象,可能需要为它授予特权。 - spencer7593
喜欢解决方案。我遇到的一个小问题是,我的表无法在存储过程中按顺序排列。哦,好吧,我会解决它 - mrwaim
还有一个障碍 - “无法从函数中访问临时表” - mrwaim
最初的问题是我们如何使用sp的结果创建临时表。这是一个很好的模式,但没有解决这个问题 - greg
greg,我的回答中的第一行说“这是对你问题的略微修改版本的回答。”你的评论是多余的。 - Christian Loris


SELECT  *
INTO    #tmpTable
FROM    OPENQUERY(YOURSERVERNAME, 'EXEC test.dbo.prc_test 1')

101
2018-03-17 10:50



获取“消息208,级别16,状态1,行1无效对象名称't​​mpBusLine'(可能因为它没有预先定义)。 - Ferdeen
@Ferds:对不起,起初并不理解你的要求。更新了其他解决方案。 - Quassnoi
好的解决方案有一点需要注意,您需要在服务器上启用“DATA ACCESS”:EXEC sp_serveroption'TheServerName','DATA ACCESS',TRUE - jcollum
您还需要允许远程访问服务器。这将产生安全后果。 - BraveNewMath
如果目标存储过程使用临时表,则无效 - Sal


最简单的解决方案

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

如果您不知道架构,则可以执行以下操作。 请 请注意,此方法存在严重的安全风险。

SELECT * 
INTO #temp
FROM OPENROWSET('SQLNCLI', 
                'Server=localhost;Trusted_Connection=yes;', 
                'EXEC [db].[schema].[sproc]')

91
2018-03-13 19:38



如果我不知道返回的结果集的列然后???我的意思是列可能会有所不那么如何将结果插入临时表??? - SHEKHAR SHETE
您可以使用OPENQUERY,但不建议使用它,因为它带有安全漏洞。 - Tigerjz32
“如果我不知道返回的结果集的列然后”,那么你不能在你的逻辑中使用它。如果您不知道它是什么,您将如何使用这些数据? - Adriaan Davel
@AdriaanDavel我同意你的看法,你应该总是知道你的数据(最佳实践),但是他可能会说的是,有时sproc会返回动态列,而你并不总是知道架构会是什么样子。在这种情况下,您可以使用OPENROWSET即时插入和创建表。但是,这样做存在明显的安全隐患...... - Tigerjz32
SQLNCLI对我无效?你怎么知道这是OLEDB提供商? - Danny Rancher


当存储过程返回大量列并且您不想手动“创建”临时表来保存结果时,我发现最简单的方法是进入存储过程并在其上添加“into”子句最后一个select语句并将1 = 0添加到where子句。

运行存储过程一次,然后返回并删除刚刚添加的SQL代码。现在,您将拥有一个与存储过程的结果匹配的空表。您可以为临时表“脚本表创建”,也可以直接插入该表。


81
2018-03-17 14:06



+1,优秀的建议。您甚至可以将一个快速可选变量添加到名为@TableCreate的sproc或类似的东西,当不为null时执行上述步骤。一旦设置好,不需要更改sproc。 - Ian Roke
@dotjoe你做了吗? SELECT INTO 一个临时表,并从临时表创建一个脚本表?临时表出现在 tempdb 但我无法右键单击并执行创建脚本。任何帮助表示赞赏。 - DotnetDude
@DotNetDude你可以 select ... into new_table 隐式创建一个实际的表。 - dotjoe
然后从空表模式中获取粗略列定义;最后用合法的TABLE_NAME替换'...': declare @s varchar(max)='';select @s=@s+','+COLUMN_NAME+' '+DATA_TYPE+isnull('('+case CHARACTER_MAXIMUM_LENGTH when -1 then 'max' else cast(CHARACTER_MAXIMUM_LENGTH as varchar(10))end+')','')from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='...';select @s - user423430


declare @temp table
(
    name varchar(255),
    field varchar(255),
    filename varchar(255),
    filegroup varchar(255),
    size varchar(255),
    maxsize varchar(255),
    growth varchar(255),
    usage varchar(255)
);
INSERT @temp  Exec sp_helpfile;
select * from @temp;

61
2018-03-24 07:15



不解决OP原始问题,首先执行插入而不定义临时表。 - t.durden


您的存储过程是否仅检索数据或修改它?如果它仅用于检索,则可以将存储过程转换为函数并使用公用表表达式(CTE)而无需声明它,如下所示:

with temp as (
    select * from dbo.fnFunctionName(10, 20)
)
select col1, col2 from temp

但是,无论需要从CTE检索什么,都只能在一个声明中使用。你做不到 with temp as ... 并尝试在几行SQL之后使用它。您可以在一个语句中包含多个CTE,以进行更复杂的查询。

例如,

with temp1020 as (
    select id from dbo.fnFunctionName(10, 20)
),
temp2030 as (
    select id from dbo.fnFunctionName(20, 30)
)
select * from temp1020 
where id not in (select id from temp2030)

40
2017-08-06 07:28



这些不是临时表,是CTE。 technet.microsoft.com/en-us/library/... - yucer
谢谢@yucer ...我相信我不知道他们当时被称为CTE :) - Rashmi Pandit