sql - 插入结果的Stored Procedure到Temporary Table

  显示原文与译文双语对照的内容

我怎么做 SELECT * INTO [temp table] FROM [stored procedure] 不是 FROM [Table] 并且未定义 [temp table]

选择所有数据从 BusinessLine成 tmpBusLine效果很好。


select * 
into tmpBusLine
from BusinessLine

尝试相同的方法,但使用返回数据的存储过程并不完全相同。


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

输出消息:

Msg 156,级别 15,状态 1,第 2行在关键字'exec'附近的语法错误。

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

时间:

你可以使用 OPENROWSET 。 查看一下。我还包括了sp_configure代码来启用即席分布式查询,以防还没有启用。


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

如果你不想先声明临时表,可以尝试创建一个user-defined函数而不是存储过程,使user-defined函数返回一个表。 Alternativly,如果你想使用存储过程,请尝试以下方法:


CREATE TABLE #tmpBus
(
 COL1 INT,
 COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'

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


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

这是对你的问题稍加修改的答案。 如果你可以放弃用户定义函数的存储过程,你可以使用内联 table-value user-defined函数。 这实际上是一个存储过程( 将获取参数),它将一个表作为结果集返回;因此将很好地放置到一个语句中。

下面是一个很好的快速文章及其他用户defiend函数。 如果你仍然需要一个存储过程的驱动,你可以用存储过程来包装内联 table-value user-defined函数。 存储过程只在调用 SELECT * FROM 时传递参数内联 table-value user-defined函数。

例如你有一个内联 table-value user-defined函数来获取一个特定区域的客户列表:


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

我认为这是获得期望结果的最'hack-less'方法。 它使用了现有的特性,因为它们本来就不需要额外的复杂性。 通过在存储过程中嵌套内联 table-value user-defined函数,你可以通过两种方式访问该功能。 另外对于实际的SQL代码,你只有一个维护点。

已经建议使用 OPENROWSET,但这不是OPENROWSET函数用于( 从联机丛书中)的目的:

包括从oledb数据源访问远程数据所需的所有连接信息。 这里方法是访问链接服务器中的表的替代方法,是使用 OLE DB连接和访问远程数据的一次性临时方法。 有关oledb数据源的更多引用,请使用链接服务器。

使用OPENROWSET将完成任务,但这将导致打开本地连接和封送数据的额外开销。 它还需要即席查询权限,可能不需要。 同时,OPENROWSET方法将排除返回多个结果集的存储过程。 在单个存储过程中包装多个内联 table-value user-defined函数可以实现这里操作。


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

当存储过程返回大量的列和你不想手动"创建"临时表来保存结果,我发现最简单的方法是进入存储过程并添加一个"进入"条款最后select语句和添加 1 =0where子句。

运行存储过程一次并返回并删除刚才添加的SQL代码。 现在,你将有一个与存储过程的结果相匹配的空表格。 你可以选择一个临时表,或者直接插入该表。


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;

你的存储过程只检索数据还是修改它? 如果只用于检索,你可以将存储过程转换为函数并使用公共表表达式( CTEs ),而不必声明它,如下所示:


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

但是,需要从CTE检索的任何内容仅在一个语句中使用。 你不能做一个 with temp as.. . 并尝试在几行SQL之后使用它。 你可以在一个语句中有多个子域,用于更复杂的查询。

比如,


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)

Quassnoi把我放在那里,但有一件事丢失了:

我需要在存储过程中使用参数的* *

并且OPENQUERY不允许这样做:

所以我找到了一个方法来处理这个系统,也不必使表定义如此严格,并在另一个存储过程( 当然,它可能会打破) 中重新定义它 !

是,你可以使用带有伪 varaiables ( 第从assamesubject英国考试和测验科当事国菲尔andinthe若干萨米立场asa good数据与数据集)的OPENQUERY语句动态创建从存储过程返回的表定义。

一旦创建了表,就可以在临时表格中使用exec存储过程。


要注意( 如上所述如上所示),你必须启用数据访问,


EXEC sp_serveroption 'MYSERVERNAME', 'DATA ACCESS', TRUE


代码:


declare @locCompanyId varchar(8)
declare @locDateOne datetime
declare @locDateTwo datetime

set @locDateOne = '2/11/2010'
set @locDateTwo = getdate()

--Build temporary table (based on bogus variable values)
--because we just want the table definition and
--since openquery does not allow variable definitions...
--I am going to use bogus variables to get the table defintion.

select * into #tempCoAttendanceRpt20100211
FROM OPENQUERY(DBASESERVER,
 'EXEC DATABASE.dbo.Proc_MyStoredProc 1,"2/1/2010","2/15/2010 3:00 pm"')

set @locCompanyId = '7753231'

insert into #tempCoAttendanceRpt20100211
EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo

set @locCompanyId = '9872231'

insert into #tempCoAttendanceRpt20100211
EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo

select * from #tempCoAttendanceRpt20100211
drop table #tempCoAttendanceRpt20100211

感谢你最初提供的信息。。 是的,最后我不需要创建所有这些虚假 ( 严格) 表defintions使用数据从另一个存储过程或数据库时,和是的可以使用参数。

搜索参考标记:

  • SQL 2005存储过程到临时表

  • 带存储过程和变量 2005的openquery

  • 带变量的openquery

  • 执行存储过程到临时表

更新:这不会使用临时表所以我不得不求助于手动创建临时表。

无赖通知︰这不能与临时表http://www.sommarskog.se/share_data.html#OPENQUERY

参考:下一步是定义 LOCALSERVER 。 它在本例中可能看起来像关键字,但实际上它只是一个名字。 这就是你的方法:


sp_addlinkedserver @server = 'LOCALSERVER', @srvproduct = '',
 @provider = 'SQLOLEDB', @datasrc = @@servername

要创建链接服务器,你必须具有权限更改任何服务器,或者是系统管理员或者setupadmin的任何固定服务器角色的成员。

OPENQUERY打开一个到 SQL Server的新连接。 这有一些含义:

用OPENQUERY调用的过程不能引用在当前连接中创建的临时表。

新连接有自己的默认数据库( 用sp_addlinkedserver定义,默认为 master ),因此所有对象规范必须包含一个数据库名称。

如果你有一个打开的事务,并且在调用OPENQUERY时持有锁,那么调用的过程不能访问你所锁定的。 也就是说,如果你不小心,你会阻止你自己。

连接不是免费的,所以有一个性能损失。

如果存储过程的结果表过于复杂,无法手工键入"创建表格"语句,并且你不能使用OPENQUERY或者 OPENROWSET,你可以使用sp_help来生成列和数据类型的列表。 一旦你有了列的列表,你就需要格式化它来满足你的需求。

1步:添加"进入 #temp" 输出查询(e。g。 "选择 [...] #temp 从 [...]") 。

最简单的方法是直接在流程中编辑输出查询。 如果不能更改存储过程,可以将内容复制到新的查询窗口,然后修改查询。

步骤 2: 在临时表中运行 sp_help 。 ( 例如"执行 tempdb 。sp_help #temp") 。

创建临时表之后,在临时表上运行sp_help以获取列和数据类型的列表,包括varchar字段的大小。

3步:复制数据列&类型到 createtable语句

我有一个用于将sp_help输出格式化为"创建表格"语句的Excel工作表。 你不需要任何花哨的东西,只需复制并粘贴到你的SQL编辑器中。 使用列名称,大小和类型构造一个"创建表 #x [...]"或者"声明 @x 表 [...]"语句,你可以使用它插入存储过程的结果。

步骤 4: 插入到新创建的表

现在你将有一个类似于这个线程中描述的其他解决方案的查询。


DECLARE @t TABLE 
(
 --these columns were copied from sp_help
 COL1 INT,
 COL2 INT 
)

INSERT INTO @t 
Exec spMyProc 

这里技术还可以用于将哈希表( #temp ) 转换为表变量( @temp ) 。 虽然这可能比自己编写 create table语句更多,但它防止了手工错误,比如在大型进程中输入错误和数据类型不匹配。 调试一个印刷错误比在第一个地方写查询需要更多时间。

...