如何执行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”。

我读过几个创建与输出存储过程结构相同的临时表的示例,这很好,但最好不要提供任何列。


当前回答

此存储过程执行以下任务:

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表,然后将其删除。

其他回答

这是一个简单的2步过程:-创建临时表-插入临时表。

执行相同操作的代码:

CREATE TABLE #tempTable (Column1 int, Column2 varchar(max));
INSERT INTO #tempTable 
EXEC [app].[Sproc_name]
@param1 = 1,
@param2 =2;

这是我的T-SQL和参数

--require one time execution if not configured before
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO

--require one time execution if not configured before
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO


--the query
DECLARE @param1 int = 1, @param2 int = 2
DECLARE @SQLStr varchar(max) = 'SELECT * INTO #MyTempTable
                                FROM OPENROWSET(''SQLNCLI'',  
''Server=ServerName;Database=DbName;Trusted_Connection=yes'',
''exec StoredProcedureName '+ CAST(@param1 AS varchar(15)) +','+ CAST(@param2 AS varchar(15)) +''') AS a ;
 select * from #MyTempTable;
 drop table #MyTempTable        
';
EXECUTE(@SQLStr);

这是对您的问题稍作修改的答案。如果可以放弃对用户定义函数使用存储过程,则可以使用内联表值用户定义函数。这本质上是一个存储过程(将采用参数),它将表作为结果集返回;因此将与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方法将排除使用返回多个结果集的存储过程。在单个存储过程中包装多个内联表值用户定义函数可以实现这一点。

我遇到了同样的问题,这是我根据保罗的建议所做的。这里的主要部分是使用NEWID()来避免多个用户同时运行存储过程/脚本,这是全局临时表的难题。

DECLARE @sql varchar(max) = '', 
@tmp_global_table varchar(255) = '##global_tmp_' + CONVERT(varchar(36), NEWID())
SET @sql = @sql + 'select * into [' + @tmp_global_table + '] from YOURTABLE'
EXEC(@sql)

EXEC('SELECT * FROM [' + @tmp_global_table + ']')

最简单的解决方案:创建表#temp(…);插入#tempEXEC[sproc];

如果您不知道模式,那么可以执行以下操作。请注意,这种方法存在严重的安全风险。

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