列的类型与UNPIVOT列表中指定的其他列的类型冲突
问题描述:
我在两个单独的数据库中有两个表,我试图合并在一起以创建资产及其读数列表。我想出了以下查询:列的类型与UNPIVOT列表中指定的其他列的类型冲突
use [db1]
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @colsUnpivot
= stuff((select ','+quotename(C.name)
FROM sys.columns c
where c.object_id = OBJECT_ID('db1.dbo.RUNTIME') and c.name != 'timestamp'
for xml path('')), 1, 1, '')
use [db2]
set @query = '
SELECT
a.[ID],
a.[ASSETCLASS],
a.[ASSETID],
a.[READINGNAME],
CONVERT(nvarchar(max), a.[SCADA_TAG]) as [SCADA_TAG],
b.timestamp AS [READINGDATE],
b.scada_value AS [READING]
/*,[PROCESSED]*/ FROM [scada].[PREPROCESSING] a
LEFT JOIN (SELECT
[timestamp],
CONVERT(nvarchar(max), [SCADA_TAG]) as [SCADA_TAG],
[SCADA_VALUE]
FROM [db1].[dbo].[RUNTIME] UNPIVOT (SCADA_VALUE FOR SCADA_TAG IN ('+ @colsUnpivot +')) u) b
ON a.scada_tag = b.scada_tag'
exec sp_executesql @query;
但是,运行查询时,我收到以下错误信息:
当我看着提到列的数据类型,db1数据库对于我试图选择的列有两种不同的数据类型 - float和int。我试图将这些列名称从db1放入db2的列是数据类型nvarchar(100)。我已经尝试投射和转换SCADA_TAG没有成功。我刚刚从另一个开发人员继承了这个SQL集成,并且因为pivot和unpivot功能而生锈。任何帮助或建议将不胜感激。
答
我知道这可能会很晚。但是你可以先尝试几乎就像两个表上的模式比较。检查列或数据类型是否不相同。见下面的脚本。
SELECT c.name AS ColumnName,
t.name AS DataType,
c.max_length
FROM [DB1].sys.columns c
INNER JOIN [DB1].sys.types t
ON c.user_type_id=t.user_type_id
WHERE OBJECT_NAME(object_id) = 'TableName'
EXCEPT
SELECT c.name AS ColumnName,
t.name AS DataType,
c.max_length
FROM [DB2].sys.columns c
INNER JOIN [DB2].sys.types t
ON c.user_type_id=t.user_type_id
WHERE OBJECT_NAME(OBJECT_ID) = 'TableName'
答
我最终什么事做了,这可能不是处理这一点,从源数据库中获取的列和转换,并选择它们变成NVARCHAR(100)列的最有效方式。
USE [db1]
DECLARE @tagNames AS nvarchar(max),
@query AS nvarchar(max)
SELECT
@tagNames
= STUFF((SELECT
', CAST(' + QUOTENAME(C.name) + ' as nvarchar(100)) as ' + QUOTENAME(c.name)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('db1.dbo.RUNTIME')
AND c.name != 'timestamp'
FOR xml PATH (''))
, 1, 1, '')
USE [db2]
SET @query = 'select [timestamp], ' + @tagNames + ' into test_runtime from
[db1].dbo.RUNTIME'
EXEC sp_executesql @query
这使我具有相同的结构和值作为原始但所有的列的表已被转换到NVARCHAR与[时间戳]列除外。我就能够运行以下SQL返回非透视和合并后的结果集资产和读数:
DECLARE @colsUnpivot AS nvarchar(max),
@query AS nvarchar(max)
SELECT
@colsUnpivot
= STUFF((SELECT
',' + QUOTENAME(C.name)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('dbo.test_RUNTIME')
AND c.name != 'timestamp'
FOR xml PATH (''))
, 1, 1, '')
SET @query = '
SELECT
a.[ID],
a.[ASSETCLASS],
a.[ASSETID],
a.[READINGNAME],
a.[SCADA_TAG],
b.timestamp AS [READINGDATE],
b.scada_value AS [READING]
FROM [scada].[PREPROCESSING] a
LEFT JOIN (SELECT
[timestamp],
[scada_tag],
[SCADA_VALUE]
FROM [dbo].[test_RUNTIME] UNPIVOT (SCADA_VALUE FOR SCADA_TAG IN (' + @colsUnpivot + ')) u) b
ON a.scada_tag = b.scada_tag'
EXEC sp_executesql @query;
GO
如果有一个更有效的方法,我所有的耳朵。
如果您对列中的所有行进行反转 - 必须是相同的数据类型。 – gofr1