如何执行SELECT*INTO[temp table]FROM[存储过程]?不是FROM[Table]并且没有定义[temp Table]?
选择BusinessLine中的所有数据到tmpBusLine工作正常。
select *
into tmpBusLine
from BusinessLine
我也在尝试同样的方法,但使用返回数据的存储过程并不完全相同。
select *
into tmpBusLine
from
exec getBusinessLineHistory '16 Mar 2009'
输出消息:
消息156,级别15,状态1,第2行关键字附近的语法不正确“exec”。
我读过几个创建与输出存储过程结构相同的临时表的示例,这很好,但最好不要提供任何列。
这是对您的问题稍作修改的答案。如果可以放弃对用户定义函数使用存储过程,则可以使用内联表值用户定义函数。这本质上是一个存储过程(将采用参数),它将表作为结果集返回;因此将与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,但这不是OPENROWET函数的用途(摘自联机丛书):
包括所有连接信息访问远程数据所需的来自OLE DB数据源。这方法是访问表,并且是一个一次性临时连接方法和使用OLE访问远程数据数据库。更频繁的参考OLE DB数据源,使用链接服务器。
使用OPENROWSET可以完成任务,但在打开本地连接和编组数据时会产生一些额外的开销。它也可能不是所有情况下的选项,因为它需要临时查询权限,这会带来安全风险,因此可能不需要。此外,OPENROWSET方法将排除使用返回多个结果集的存储过程。在单个存储过程中包装多个内联表值用户定义函数可以实现这一点。
如果查询不包含参数,请使用OpenQuery,否则使用OpenRowset。
基本的事情是根据存储过程创建模式并插入到该表中。例如。:
DECLARE @abc TABLE(
RequisitionTypeSourceTypeID INT
, RequisitionTypeID INT
, RequisitionSourcingTypeID INT
, AutoDistOverride INT
, AllowManagerToWithdrawDistributedReq INT
, ResumeRequired INT
, WarnSupplierOnDNRReqSubmission INT
, MSPApprovalReqd INT
, EnableMSPSupplierCounterOffer INT
, RequireVendorToAcceptOffer INT
, UseCertification INT
, UseCompetency INT
, RequireRequisitionTemplate INT
, CreatedByID INT
, CreatedDate DATE
, ModifiedByID INT
, ModifiedDate DATE
, UseCandidateScheduledHours INT
, WeekEndingDayOfWeekID INT
, AllowAutoEnroll INT
)
INSERT INTO @abc
EXEC [dbo].[usp_MySp] 726,3
SELECT * FROM @abc
此存储过程执行以下任务:
CREATE PROCEDURE [dbo].[ExecIntoTable]
(
@tableName NVARCHAR(256),
@storedProcWithParameters NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @driver VARCHAR(10)
DECLARE @connectionString NVARCHAR(600)
DECLARE @sql NVARCHAR(MAX)
DECLARE @rowsetSql NVARCHAR(MAX)
SET @driver = '''SQLNCLI'''
SET @connectionString =
'''server=' +
CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(256)) +
COALESCE('\' + CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(256)), '') +
';trusted_connection=yes'''
SET @rowsetSql = '''EXEC ' + REPLACE(@storedProcWithParameters, '''', '''''') + ''''
SET @sql = '
SELECT
*
INTO
' + @tableName + '
FROM
OPENROWSET(' + @driver + ',' + @connectionString + ',' + @rowsetSql + ')'
EXEC (@sql)
END
GO
这是一个轻微的修改:将存储过程结果插入到表中,使其实际工作。
如果希望它与临时表一起工作,则需要使用##GLOBAL表,然后将其删除。
如果您有幸拥有SQL 2012或更高版本,可以使用dm_exec_descript_first_result_set_for_object
我刚刚编辑了gotqn提供的sql。谢谢你。
这将创建一个名称与过程名称相同的全局临时表。以后可以根据需要使用临时表。只是不要忘记在重新执行之前删除它。
declare @procname nvarchar(255) = 'myProcedure',
@sql nvarchar(max)
set @sql = 'create table ##' + @procname + ' ('
begin
select @sql = @sql + '[' + r.name + '] ' + r.system_type_name + ','
from sys.procedures AS p
cross apply sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
where p.name = @procname
set @sql = substring(@sql,1,len(@sql)-1) + ')'
execute (@sql)
execute('insert ##' + @procname + ' exec ' + @procname)
end