如何执行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”。
我读过几个创建与输出存储过程结构相同的临时表的示例,这很好,但最好不要提供任何列。
如果存储过程的结果表太复杂,无法手动键入“createtable”语句,并且不能使用OPENQUERY或OPENROWSET,则可以使用sp_help为您生成列和数据类型列表。一旦您有了列列表,就只需要将其格式化以满足您的需要。
步骤1:将“into#temp”添加到输出查询中(例如“select[…]into#TEMPfrom[…]”)。
最简单的方法是直接在proc中编辑输出查询。如果无法更改存储的proc,可以将内容复制到新的查询窗口中,并在其中修改查询。
步骤2:对临时表运行sp_help。(例如“exec tempdb..sp_help#temp”)
创建临时表后,对临时表运行sp_help以获取列和数据类型的列表,包括varchar字段的大小。
步骤3:将数据列和类型复制到createtable语句中
我有一个Excel工作表,用于将sp_help的输出格式化为“createtable”语句。您不需要任何花哨的东西,只需复制并粘贴到SQL编辑器中即可。使用列名、大小和类型构造“Createtable#x[…]”或“declare@xtable[…]“语句,您可以使用该语句插入存储过程的结果。
步骤4:插入新创建的表
现在,您将得到一个与本主题中描述的其他解决方案类似的查询。
DECLARE @t TABLE
(
--these columns were copied from sp_help
COL1 INT,
COL2 INT
)
INSERT INTO @t
Exec spMyProc
此技术也可用于将临时表(#temp)转换为表变量(@temp)。虽然这可能比自己编写createtable语句要简单得多,但它可以防止大型进程中出现诸如拼写错误和数据类型不匹配等手动错误。调试拼写错误可能比一开始编写查询要花费更多的时间。
这是我的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);
如果您有幸拥有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