sql-server - 将动态查询导出到表

Question

我想将具有动态列的动态查询的结果导出到临时表。由于列是动态的，我无法预先定义架构。

尝试使用 Select.. Into 来使用临时表，但是这不起作用并且没有输出任何表。

declare @start DATETIME = (select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-13, 0))  
declare @end DATETIME = (select  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) 
declare @v_columns varchar(max)
declare @v_sql varchar(max)

;with months (v_date)
AS
(
    SELECT @start
    UNION ALL
    SELECT DATEADD(month,1,v_date)
    from months
    where DATEADD(month,1,v_date)<=@end
)
select @v_columns=stuff((select','+QUOTENAME(DATENAME(month,v_date)+cast(year(v_date) as varchar)) from months for xml path('')),1,1,'')
print @v_columns
set @v_sql='
;with months (v_date)
AS
(
    SELECT cast('''+convert(varchar(10),@start,101)+''' as date)
    UNION ALL
    SELECT DATEADD(month,1,v_date)
    from months
    where DATEADD(month,1,v_date)<='''+convert(varchar(10),@end,101)+'''
)

SELECT 
 INVOICECUSTOMERACCOUNTNUMBER, ORGANIZATIONNAME,'+@v_columns+'
INTO #newtbl FROM 
(

SELECT 
b.INVOICECUSTOMERACCOUNTNUMBER, c.ORGANIZATIONNAME,SUM(a.LINEAMOUNTMST) [LineAmount],DATENAME(month,a.invoicedate)+cast(year(a.invoicedate) as varchar) as v_date
from dbo.Table1 a

inner join dbo.Table2 b
on a.INVOICEID=b.INVOICENUMBER
inner join dbo.Table3 c 
on b.INVOICECUSTOMERACCOUNTNUMBER=c.CUSTOMERACCOUNT
group by b.INVOICECUSTOMERACCOUNTNUMBER,c.ORGANIZATIONNAME,a.INVOICEDATE) as x
PIVOT
(
 SUM(LineAmount) FOR v_date IN ('+@v_columns+')
) PVT; SELECT * FROM #newtbl'

print @v_sql

EXEC (@v_sql)

使用动态列名及其值动态创建的表。

Answer 1

我认为您的临时表超出了范围。不要使用本地临时表(一个#)，而是尝试使用全局临时表(两个#)。

查看此 question (和回答)以获取有关本地和全局临时表的更多背景信息。