将动态生成的数据透视表放入临时表
我见过 这个，所以我知道如何使用动态生成的一组字段创建数据透视表.我现在的问题是我想将结果放入一个临时表中.
I've seen this, so I know how to create a pivot table with a dynamically generated set of fields. My problem now is that I'd like to get the results into a temporary table.
我知道，为了从 EXEC 语句将结果集放入临时表中，您需要预定义临时表.如果是动态生成的数据透视表，则无法事先知道字段.
I know that in order to get the result set into a temp table from an EXEC statement you need to predefine the temp table. In the case of a dynamically generated pivot table, there is no way to know the fields beforehand.
我能想到的获得此类功能的唯一方法是使用动态 SQL 创建一个永久表.有没有更好的办法?
The only way I can think of to get this type of functionality is to create a permanent table using dynamic SQL. Is there a better way?
推荐答案
你可以这样做:
-- add 'loopback' linkedserver
if exists (select * from master..sysservers where srvname = 'loopback')
exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
@srvproduct = N'',
@provider = N'SQLOLEDB',
@datasrc = @@servername
go
declare @myDynamicSQL varchar(max)
select @myDynamicSQL = 'exec sp_who'
exec('
select * into #t from openquery(loopback, ''' + @myDynamicSQL + ''');
select * from #t
')
添加动态 sql 以接受 openquery 的参数
addded dynamic sql to accept params to openquery
相关文章