如何执行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”。
我读过几个创建与输出存储过程结构相同的临时表的示例，这很好，但最好不要提供任何列。
当前回答
这可以在SQL Server 2014+中完成，前提是存储过程只返回一个表。如果有人能为多张桌子找到这样的方法，我很想知道。
DECLARE @storedProcname NVARCHAR(MAX) = ''
SET @storedProcname = 'myStoredProc'
DECLARE @strSQL AS VARCHAR(MAX) = 'CREATE TABLE myTableName '
SELECT @strSQL = @strSQL+STUFF((
SELECT ',' +name+' ' + system_type_name
FROM sys.dm_exec_describe_first_result_set_for_object (OBJECT_ID(@storedProcname),0)
FOR XML PATH('')
),1,1,'(') + ')'
EXEC (@strSQL)
INSERT INTO myTableName
EXEC ('myStoredProc @param1=1, @param2=2')
SELECT * FROM myTableName
DROP TABLE myTableName
这将从系统表中提取返回表的定义，并使用它为您构建临时表。然后，您可以如前所述从存储过程填充它。
还有一些变体也适用于动态SQL。
其他回答
在四处搜索之后，我找到了一种方法，可以在不使用OPENROWSET或OPENQUERY的情况下，使用存储过程结果定义的通用模式为任何存储过程动态创建临时表，特别是当您不是数据库管理员时。
Sql服务器有一个内置过程sp_describe_first_result_set，它可以为您提供任何过程结果集的模式。我根据这个过程的结果创建了一个模式表，并手动将所有字段设置为NULLABLE。
declare @procname varchar(100) = 'PROCEDURENAME' -- your procedure name
declare @param varchar(max) = '''2019-06-06''' -- your parameters
declare @execstr nvarchar(max) = N'exec ' + @procname
declare @qry nvarchar(max)
-- Schema table to store the result from sp_describe_first_result_set.
create table #d
(is_hidden bit NULL, column_ordinal int NULL, name sysname NULL, is_nullable bit NULL, system_type_id int NULL, system_type_name nvarchar(256) NULL,
max_length smallint NULL, precision tinyint NULL, scale tinyint NULL, collation_name sysname NULL, user_type_id int NULL, user_type_database sysname NULL,
user_type_schema sysname NULL,user_type_name sysname NULL,assembly_qualified_type_name nvarchar(4000),xml_collection_id int NULL,xml_collection_database sysname NULL,
xml_collection_schema sysname NULL,xml_collection_name sysname NULL,is_xml_document bit NULL,is_case_sensitive bit NULL,is_fixed_length_clr_type bit NULL,
source_server sysname NULL,source_database sysname NULL,source_schema sysname NULL,source_table sysname NULL,source_column sysname NULL,is_identity_column bit NULL,
is_part_of_unique_key bit NULL,is_updateable bit NULL,is_computed_column bit NULL,is_sparse_column_set bit NULL,ordinal_in_order_by_list smallint NULL,
order_by_list_length smallint NULL,order_by_is_descending smallint NULL,tds_type_id int NULL,tds_length int NULL,tds_collation_id int NULL,
tds_collation_sort_id tinyint NULL)
-- Get result set definition of your procedure
insert into #d
EXEC sp_describe_first_result_set @exestr, NULL, 0
-- Create a query to generate and populate a global temp table from above results
select
@qry = 'Create table ##t(' +
stuff(
(select ',' + name + ' '+ system_type_name + ' NULL'
from #d d For XML Path, TYPE)
.value(N'.[1]', N'nvarchar(max)')
, 1,1,'')
+ ')
insert into ##t
Exec '+@procname+' ' + @param
Exec sp_executesql @qry
-- Use below global temp table to query the data as you may
select * from ##t
-- **WARNING** Don't forget to drop the global temp table ##t.
--drop table ##t
drop table #d
在Sql Server版本-Microsoft Sql Server 2016（RTM）-13.0.1601.5（版本17134:）上开发和测试
您可以调整正在使用的SQL server版本的模式（如果需要）。
您可以为此使用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
这个问题晚了几年，但我需要像这样的东西来快速生成肮脏的代码。我相信正如其他人所说的那样，预先定义临时表更容易，但这种方法应该适用于简单的存储过程查询或sql语句。
这将有点复杂，但它借鉴了这里的贡献者以及Paul White的DBA Stack Exchange Get存储过程结果列类型解决方案。再次重申，此方法和示例不是为多用户环境中的流程设计的。在这种情况下，表定义在全局临时表中被设置了一段时间，以供代码生成模板过程参考。
我还没有完全测试过这一点，所以可能会有一些警告，所以您可能需要转到Paul White的答案中的MSDN链接。这适用于SQL 2012及更高版本。
首先使用存储过程sp_describe_First_result_set，它类似于Oracle的description。
这将计算第一个结果集的第一行，因此如果存储过程或语句返回多个查询，它将只描述第一个结果。
我创建了一个存储过程来分解返回单个字段以创建临时表定义的任务。
CREATE OR ALTER PROCEDURE [dbo].[sp_GetTableDefinitionFromSqlBatch_DescribeFirstResultSet]
(
@sql NVARCHAR(4000)
,@table_name VARCHAR(100)
,@TableDefinition NVARCHAR(MAX) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @TempTableDefinition NVARCHAR(MAX)
DECLARE @NewLine NVARCHAR(4) = CHAR(13)+CHAR(10)
DECLARE @ResultDefinition TABLE ( --The View Definition per MSDN
is_hidden bit NOT NULL
, column_ordinal int NOT NULL
, [name] sysname NULL
, is_nullable bit NOT NULL
, system_type_id int NOT NULL
, system_type_name nvarchar(256) NULL
, max_length smallint NOT NULL
, [precision] tinyint NOT NULL
, scale tinyint NOT NULL
, collation_name sysname NULL
, user_type_id int NULL
, user_type_database sysname NULL
, user_type_schema sysname NULL
, user_type_name sysname NULL
, assembly_qualified_type_name nvarchar(4000)
, xml_collection_id int NULL
, xml_collection_database sysname NULL
, xml_collection_schema sysname NULL
, xml_collection_name sysname NULL
, is_xml_document bit NOT NULL
, is_case_sensitive bit NOT NULL
, is_fixed_length_clr_type bit NOT NULL
, source_server sysname NULL
, source_database sysname NULL
, source_schema sysname NULL
, source_table sysname NULL
, source_column sysname NULL
, is_identity_column bit NULL
, is_part_of_unique_key bit NULL
, is_updateable bit NULL
, is_computed_column bit NULL
, is_sparse_column_set bit NULL
, ordinal_in_order_by_list smallint NULL
, order_by_is_descending smallint NULL
, order_by_list_length smallint NULL
, tds_type_id int NOT NULL
, tds_length int NOT NULL
, tds_collation_id int NULL
, tds_collation_sort_id tinyint NULL
)
--Insert the description into table variable
INSERT @ResultDefinition
EXEC sp_describe_first_result_set @sql
--Now Build the string to create the table via union select statement
;WITH STMT AS (
SELECT N'CREATE TABLE ' + @table_name + N' (' AS TextVal
UNION ALL
SELECT
CONCAT(
CASE column_ordinal
WHEN 1 THEN ' ' ELSE ' , ' END --Determines if comma should precede
, QUOTENAME([name]) , ' ', system_type_name -- Column Name and SQL TYPE
,CASE is_nullable
WHEN 0 THEN ' NOT NULL' ELSE ' NULL' END --NULLABLE CONSTRAINT
) AS TextVal
FROM @ResultDefinition WHERE is_hidden = 0 -- May not be needed
UNION ALL
SELECT N');' + @NewLine
)
--Now Combine the rows to a single String
SELECT @TempTableDefinition = COALESCE (@TempTableDefinition + @NewLine + TextVal, TextVal) FROM STMT
SELECT @TableDefinition = @TempTableDefinition
END
难题是，您需要使用全局表，但需要使其足够独特因此您可以频繁地从中删除和创建，而不必担心冲突。在示例中，我使用Guid（FE264BF5_9C32_438F_8462_8A5DC8DE49E）作为全局变量，将连字符替换为下划线
DECLARE @sql NVARCHAR(4000) = N'SELECT @@SERVERNAME as ServerName, GETDATE() AS Today;'
DECLARE @GlobalTempTable VARCHAR(100) = N'##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable'
--@sql can be a stored procedure name like dbo.foo without parameters
DECLARE @TableDef NVARCHAR(MAX)
DROP TABLE IF EXISTS #MyTempTable
DROP TABLE IF EXISTS ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
EXEC [dbo].[sp_GetTableDefinitionFromSqlBatch_DescribeFirstResultSet]
@sql, @GlobalTempTable, @TableDef OUTPUT
--Creates the global table ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
EXEC sp_executesql @TableDef
--Now Call the stored procedure, SQL Statement with Params etc.
INSERT ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
EXEC sp_executesql @sql
--Select the results into your undefined Temp Table from the Global Table
SELECT *
INTO #MyTempTable
FROM ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
SELECT * FROM #MyTempTable
DROP TABLE IF EXISTS #MyTempTable
DROP TABLE IF EXISTS ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
同样，我只使用简单的存储过程查询和简单的查询对其进行了测试，因此您的里程可能会有所不同。希望这对某人有所帮助。
Select @@ServerName
EXEC sp_serveroption @@ServerName, 'DATA ACCESS', TRUE
SELECT *
INTO #tmpTable
FROM OPENQUERY(YOURSERVERNAME, 'EXEC db.schema.sproc 1')
如果让动态SQL创建一个临时表，则该表由动态SQL连接拥有，而不是从中调用存储过程的连接。
DECLARE @COMMA_SEPARATED_KEYS varchar(MAX);
DROP TABLE IF EXISTS KV;
CREATE TABLE KV (id_person int, mykey varchar(30), myvalue int);
INSERT INTO KV VALUES
(1, 'age', 16),
(1, 'weight', 63),
(1, 'height', 175),
(2, 'age', 26),
(2, 'weight', 83),
(2, 'height', 185);
WITH cte(mykey) AS (
SELECT DISTINCT mykey FROM KV
)
SELECT @COMMA_SEPARATED_KEYS=STRING_AGG(mykey,',') FROM cte;
SELECT @COMMA_SEPARATED_KEYS AS keys;
DECLARE @ExecuteExpression varchar(MAX);
DROP TABLE IF EXISTS #Pivoted;
SET @ExecuteExpression = N'
SELECT *
INTO #Pivoted
FROM
(
SELECT
mykey,
myvalue,
id_person
FROM KV
) AS t
PIVOT(
MAX(t.myvalue)
FOR mykey IN (COMMA_SEPARATED_KEYS)
) AS pivot_table;
';
SET @ExecuteExpression = REPLACE(@ExecuteExpression, 'COMMA_SEPARATED_KEYS', @COMMA_SEPARATED_KEYS);
EXEC(@ExecuteExpression);
SELECT * FROM #Pivoted;
消息208，级别16，状态0对象名称“#Pivoted”无效。这是因为#Pivoted由动态SQL连接拥有。最后一条指令
SELECT * FROM #Pivoted
失败。
避免此问题的一种方法是确保所有对#Pivoted的引用都是从动态查询本身内部进行的：
DECLARE @COMMA_SEPARATED_KEYS varchar(MAX);
DROP TABLE IF EXISTS KV;
CREATE TABLE KV (id_person int, mykey varchar(30), myvalue int);
INSERT INTO KV VALUES
(1, 'age', 16),
(1, 'weight', 63),
(1, 'height', 175),
(2, 'age', 26),
(2, 'weight', 83),
(2, 'height', 185);
WITH cte(mykey) AS (
SELECT DISTINCT mykey FROM KV
)
SELECT @COMMA_SEPARATED_KEYS=STRING_AGG(mykey,',') FROM cte;
SELECT @COMMA_SEPARATED_KEYS AS keys;
DECLARE @ExecuteExpression varchar(MAX);
DROP TABLE IF EXISTS #Pivoted;
SET @ExecuteExpression = N'
SELECT *
INTO #Pivoted
FROM
(
SELECT
mykey,
myvalue,
id_person
FROM KV
) AS t
PIVOT(
MAX(t.myvalue)
FOR mykey IN (COMMA_SEPARATED_KEYS)
) AS pivot_table;
SELECT * FROM #Pivoted;
';
SET @ExecuteExpression = REPLACE(@ExecuteExpression, 'COMMA_SEPARATED_KEYS', @COMMA_SEPARATED_KEYS);
EXEC(@ExecuteExpression);
