在不知道表定义的情况下将动态数据透视结果插入现有表中 [英] Inserting dynamic pivot result into an existing table without knowing table definition

问题描述

我有一个动态数据透视表，它在运行前生成未知数量的列.我正在尝试从现有表中清除数据并将查询结果插入其中.我遇到的问题是我不知道表定义会提前是什么，因为它是动态的.

这是我得到的错误:
Msg 213, Level 16, State 7, Line 1列名或提供的值数量与表定义不匹配.

透视列是一个日期列，可以有 1 个日期或 1000 多个日期.如何创建与我尝试插入的数据相匹配的表定义?

这是我的查询:

 DECLARE @colsPivot AS NVARCHAR(MAX),@colsUnpivot AS NVARCHAR(MAX)，@query AS NVARCHAR(MAX)选择@colsPivot = STUFF((SELECT ',' + QUOTENAME(rce.EcoDate)来自 PhdRpt.RptCaseEco_542 AS rce按 rce.EcoDate 分组按 rce.EcoDate 排序FOR XML PATH(''), 类型).value('.', 'NVARCHAR(MAX)'),1,1,'')SELECT @colsUnpivot = stuff((SELECT ','+quotename(C.name)FROM sys.columns AS CWHERE C.object_id = object_id('PhdRpt.RptCaseEco_542') ANDC.name in ('NDCash', 'DCash')--LIKE '%Cash' 或 C.Name like 'NetGas'FOR xml 路径('')), 1, 1, '')设置@query= '选择*--进入##你好从(选择ReportRunCaseId,上校，生态日期，值来自 PhdRpt.RptCaseEco_542转轴(值对于 col in ('+ @colsunpivot +')) 你) x1枢(最大值(值)用于 ('+ @colspivot +') 中的 EcoDate) p'截断表 dbo.Table1插入 dbo.Table1执行(@查询)

解决方案

我会 DROP 原始表并使用 SELECT INTO 子句动态创建新表.

通过使用SELECT INTO，您无需事先创建表或担心它将具有什么类型和多少列.SQL Server 只会为您创建它.

注意:这仅在表不存在时有效.

关于INTO 条款的完整文档http://technet.microsoft.com/en-us/library/ms188029.aspx

作为另一种选择，您始终可以SELECT INTO #temp 表，您可以删除该表或使用它来为其他表建模，但我认为这是您不想做的额外工作.

I have a dynamic pivot table that produces an unknown number of columns until runtime. I am trying to clear the data out of an existing table and insert the results of my query into it. The problem that I am having is that I do not know what the table definition will be ahead of time because it is dynamic.

Here is the error I am getting:
Msg 213, Level 16, State 7, Line 1 Column name or number of supplied values does not match table definition.

The pivoted column is a date column which could have 1 date or 1000+ dates. How can I create a table definition that will match the data that I am trying to insert?

Here is my query:

 DECLARE @colsPivot AS NVARCHAR(MAX),
 @colsUnpivot AS NVARCHAR(MAX),
 @query AS NVARCHAR(MAX)

select @colsPivot = STUFF((SELECT ',' + QUOTENAME(rce.EcoDate) 
                             from PhdRpt.RptCaseEco_542 AS rce
                             group by rce.EcoDate
                             order by rce.EcoDate
                     FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                     ,1,1,'')

SELECT @colsUnpivot = stuff((SELECT ','+quotename(C.name)
FROM sys.columns AS C
WHERE C.object_id = object_id('PhdRpt.RptCaseEco_542') AND
C.name in ('NDCash', 'DCash')--LIKE '%Cash' or C.Name like  'NetGas'
FOR xml path('')), 1, 1, '')

set @query 
 = 'select *
 --into ##hello
 from
 (
     SELECT  
        ReportRunCaseId,
        col, 
        EcoDate,  
        val
    FROM PhdRpt.RptCaseEco_542  
unpivot
(
val
for col in ('+ @colsunpivot +')
) u
 ) x1
 pivot
 (
     max(val)
     for EcoDate in ('+ @colspivot +')
 ) p'

truncate table dbo.Table1
insert into dbo.Table1
exec(@query)

解决方案

I would DROP original table and just create new one on the fly using SELECT INTO clause.

by using SELECT INTO you do not need to create table before hand or worry what type and how many columns it will have. SQL Server will just create it for you.

Note: this only works when table does not exists.

Full documentation on INTO Clause http://technet.microsoft.com/en-us/library/ms188029.aspx

as another alternative you can always SELECT INTO #temp table that you can drop or use it to model your other table but I think this is extra work that you do not want to do.

这篇关于在不知道表定义的情况下将动态数据透视结果插入现有表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！