我怎么做 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'
Output message:
消息156,级别15,状态1,行2关键字'exec'附近的语法不正确 .
我已经阅读了几个创建一个与输出存储过程结构相同的临时表的例子,它工作正常,但不提供任何列会很好 .
25 回答
Quassnoi把我的大部分路都放在那里,但有一件事遗失了:
****我需要在存储过程中使用参数 . ****
并且OPENQUERY不允许这种情况发生:
所以我找到了一种方法来处理系统,也没有必要使表定义如此严格,并在另一个存储过程中重新定义它(当然也有可能会破坏它)!
是的,您可以使用带有伪造变量的OPENQUERY语句动态创建从存储过程返回的表定义(只要NO RESULT SET返回相同数量的字段并且与具有良好数据的数据集位于相同位置) .
创建表后,您可以整天将exec存储过程用于临时表 .
并注意(如上所示)您必须启用数据访问,
代码:
感谢最初提供的信息... Yes, finally I do not have to create all these bogus (严格)表格定义使用来自另一个存储过程或数据库的数据时, yes 也可以使用参数 .
搜索参考标签:
SQL 2005存储过程进入临时表
openquery,包含存储过程和变量2005
带有变量的openquery
将执行存储过程执行到临时表中
更新: this will not work with temporary tables 所以我不得不求助于手动创建临时表 .
Bummer notice :这不适用于 temporary tables ,http://www.sommarskog.se/share_data.html#OPENQUERY
参考:接下来是定义LOCALSERVER . 它可能看起来像示例中的关键字,但它实际上只是一个名称 . 这是你如何做到的:
要创建链接服务器,您必须具有ALTER ANY SERVER权限,或者是任何固定服务器角色sysadmin或setupadmin的成员 .
OPENQUERY打开与SQL Server的新连接 . 这有一些含义:
使用OPENQUERY调用的过程无法引用当前连接中创建的临时表 .
新连接有自己的默认数据库(使用sp_addlinkedserver定义,默认为master),因此所有对象规范都必须包含数据库名称 .
如果您有一个打开的事务并且在调用OPENQUERY时持有锁,则被调用的过程无法访问您锁定的内容 . 也就是说,如果你不小心,你会阻止自己 .
连接不是免费的,因此会有性能损失 .
我发现Passing Arrays/DataTables into Stored Procedures可能会让你对如何解决问题有所了解 .
该链接建议使用 Image 类型参数传递到存储过程 . 然后在存储过程中,图像被转换为包含原始数据的表变量 .
也许有一种方法可以用于临时表 .
您的存储过程是否仅检索数据或修改它?如果它仅用于检索,则可以将存储过程转换为函数并使用公用表表达式(CTE)而无需声明它,如下所示:
但是,无论需要从CTE检索什么,都只能在一个声明中使用 . 你不能做一个
with temp as ...
并尝试在几行SQL之后使用它 . 您可以在一个语句中包含多个CTE,以进行更复杂的查询 .例如,
这是对您问题的略微修改版本的答案 . 如果您可以放弃对用户定义函数使用存储过程,则可以使用内联表值用户定义函数 . 这本质上是一个存储过程(将采用参数),它返回一个表作为结果集;因此,将很好地与INTO声明 .
这里有一个很好的quick article和其他用户定义的函数 . 如果仍然需要存储过程,则可以使用存储过程包装内联表值用户定义函数 . 当存储过程从内联表值用户定义函数调用select *时,它只传递参数 .
因此,例如,您有一个内联表值用户定义函数来获取特定区域的客户列表:
然后,您可以调用此函数来获得结果:
或者做一个SELECT INTO:
如果仍需要存储过程,则将函数包装为:
我认为这是获得预期结果的最“无懈怠”的方法 . 它使用现有功能,因为它们的使用目的没有其他复杂性 . 通过嵌套存储过程中的内联表值用户定义函数,您可以通过两种方式访问该功能 . 加!实际的SQL代码只有一个维护点 .
已经建议使用OPENROWSET,但这不是OPENROWSET函数的用途(来自联机丛书):
使用OPENROWSET将完成工作,但是它会产生一些额外的开销,用于打开本地连接和编组数据 . 它也可能不是所有情况下的选项,因为它需要临时查询许可,这会带来安全风险,因此可能不是所希望的 . 此外,OPENROWSET方法将排除使用返回多个结果集的存储过程 . 在单个存储过程中包装多个内联表值用户定义函数可以实现此目的 .
如果查询不包含参数,请使用
OpenQuery
否则使用OpenRowset
.基本的事情是根据存储过程创建模式并插入到该表中 . 例如 . :
插入#temp
EXEC [sproc];
如果您不知道架构,则可以执行以下操作 . 请注意,此方法存在严重的安全风险 .
如果你有幸拥有SQL 2012或更高版本,你可以使用
dm_exec_describe_first_result_set_for_object
我刚刚编辑了gotqn提供的sql . 谢谢你拿到了 .
这将创建一个名称与过程名称相同的全局临时表 . 稍后可以根据需要使用临时表 . 只是不要忘记在重新执行之前删除它 .
如果OPENROWSET导致您出现问题,那么从2012年开始还有另一种方式;使用sys.dm_exec_describe_first_result_set_for_object,如下所述:Retrieve column names and types of a stored procedure?
首先,创建此存储过程以生成临时的SQL
要使用该过程,请按以下方式调用它:
请注意,因为使用EXEC来运行动态SQL会创建自己的会话,所以普通的临时表将超出任何后续代码的范围 . 如果全局临时表是一个问题,您可以使用普通的临时表,但任何后续SQL都需要是动态的,也就是说,也是由EXEC语句执行的 .
Code
我希望这有帮助 . 请酌情符合资格 .
我遇到了同样的问题,这是我从Paul's suggestion为此做的 . 这里的主要部分是使用
NEWID()
来避免多个用户同时运行存储过程/脚本,为全局临时表带来痛苦 .这个存储过程完成了这项工作:
这是对此的轻微修改:Insert stored procedure results into table以便它实际上有效 .
如果您希望它与临时表一起使用,那么您将需要使用
##GLOBAL
表并在之后删除它 .当存储过程返回大量列并且您不想手动“创建”临时表来保存结果时,我发现最简单的方法是进入存储过程并在其上添加“into”子句 . 最后一个select语句并将1 = 0添加到where子句 .
运行存储过程一次,然后返回并删除刚刚添加的SQL代码 . 现在,您将拥有一个与存储过程的结果匹配的空表 . 您可以为临时表“脚本表创建”,也可以直接插入该表 .
要将存储过程的第一个记录集插入临时表,您需要知道以下内容:
只能将存储过程的第一行集插入临时表中
存储过程不能执行动态T-SQL语句(
sp_executesql
)您需要先定义临时表的结构
上面可能看起来像限制,但恕我直言它完全有道理 - 如果你使用
sp_executesql
你可以一次返回两列,一旦十,如果你有多个结果集,你也不能将它们插入到几个表中 - 你可以插入一个T-SQL语句中的两个表中的最大值(使用OUTPUT
子句且没有触发器) .因此,问题主要是如何在执行
EXEC ... INTO ...
语句之前定义临时表结构 .sys.dm_exec_describe_first_result_set_for_object
sys.dm_exec_describe_first_result_set
sp_describe_first_result_set
第一个使用
OBJECT_ID
,而第二个和第三个也使用Ad-hoc查询 . 我更喜欢使用DMV而不是sp,因为您可以使用CROSS APPLY
并同时为多个过程构建临时表定义 .另外,请注意
system_type_name
字段,因为它非常有用 . 它存储列完整定义 . 例如:你呢可以在大多数情况下直接使用它来创建表定义 .
因此,我认为在大多数情况下(如果存储过程符合某些条件),您可以轻松构建动态语句来解决此类问题(创建临时表,在其中插入存储过程结果,使用数据执行所需操作) .
请注意,上面的对象无法在某些_96247中定义第一个结果集数据,例如在执行动态T-SQL语句或在存储过程中使用临时表时 .
另一种方法是创建一个类型并使用PIPELINED然后传回你的对象 . 但这仅限于知道列 . 但它的优点是能够做到:
如果存储过程的结果表太复杂而无法手动输入“create table”语句,并且无法使用OPENQUERY OR OPENROWSET,则可以使用sp_help为您生成列和数据类型列表 . 获得列列表后,只需格式化以满足您的需求即可 .
Step 1: Add "into #temp" to the output query (e.g. "select [...] into #temp from [...]").
最简单的方法是直接在proc中编辑输出查询 . 如果您无法更改存储过程,则可以将内容复制到新的查询窗口并在那里修改查询 .
Step 2: Run sp_help on the temp table. (e.g. "exec tempdb..sp_help #temp")
创建临时表后,在临时表上运行sp_help以获取列和数据类型的列表,包括varchar字段的大小 .
Step 3: Copy the data columns & types into a create table statement
我有一个Excel工作表,我用它来将sp_help的输出格式化为“create table”语句 . 您不需要任何花哨的东西,只需复制并粘贴到您的SQL编辑器中即可 . 使用列名,大小和类型构造“创建表#x [...]”或“声明@x表[...]”语句,您可以使用该语句来插入存储过程的结果 .
Step 4: Insert into the newly created table
现在,您将拥有一个与此线程中描述的其他解决方案类似的查询 .
此技术还可用于将临时表(
#temp
)转换为表变量(@temp
) . 虽然这可能比仅自己编写create table
语句更多的步骤,但它可以防止手动错误,例如大型进程中的拼写错误和数据类型不匹配 . 调试拼写错误可能比首先编写查询花费更多时间 .您可以使用OPENROWSET . 看一看 . 我've also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn' t已经启用了 .
如果您想在不先声明临时表的情况下执行此操作,则可以尝试创建用户定义的函数而不是stored procedure,并使该用户定义的函数返回一个表 . 或者,如果要使用存储过程,请尝试以下操作:
我正在创建一个包含以下架构和数据的表 .
创建存储过程 .
现在我知道我的程序结果是什么,所以我正在执行以下查询 .
VALUES(10,5,1,NULL)SET IDENTITY_INSERT [dbo] . [tblTestingTree] On
好吧,你必须创建一个临时表,但它不必具有正确的模式....我已经创建了一个存储过程来修改现有的临时表,以便它具有包含正确数据的必需列类型和顺序(删除所有现有列,添加新列):
_996262_这赢得了't work if sys.dm_exec_describe_first_result_set_for_object can' t确定存储过程的结果(例如,如果它使用临时表) .
我会做以下事情
创建(转换SP)UDF(表值UDF) .
select * into #tmpBusLine from dbo.UDF_getBusinessLineHistory '16 Mar 2009'
在SQL Server 2005中,您可以使用
INSERT INTO ... EXEC
将存储过程的结果插入表中 . 从MSDN's INSERT documentation(对于SQL Server 2000,实际上):如果您知道正在传递的参数,并且您无权访问make sp_configure,则使用这些参数编辑存储过程,并将其存储在##全局表中 .
这可以在SQL Server 2014中完成,前提是SP只返回一个表 . 如果有人为多个表找到了这样做的方法,我很乐意了解它 .
这将从系统表中提取返回表的定义,并使用它来为您构建临时表 . 然后,您可以按照前面的说明从SP填充它 .
此外,还有一些变体可以与Dynamic SQL一起使用 .