问如何识别在T中导致数据类型错误的所有列？
EN

Stack Overflow用户

提问于 2021-06-30 01:44:55

回答 3查看 443关注 0票数 0

我正试图在两张大表( 600多列)上执行联合操作。两个表中的所有变量都应该具有相同的格式，但它们是由两个不同的团队单独准备的。

SELECT * into #FINAL_TABLE
from 
(select * from Table1
UNION
select * FROM Table2 ) as tmp

我得到一个错误，其中包括一列中有关数据类型错误的信息：

Msg 8114，16级，状态5，线路600

将数据类型nvarchar转换为数字时出错。

基于这个错误，我不能说：

哪个列导致了错误？
还有其他列也会导致错配错误吗？

是否有更有效的方法来识别所有导致日期类型错配错误的列，然后逐列检查？

sql

sql-server

tsql

回答 3

Stack Overflow用户

回答已采纳

发布于 2021-06-30 02:03:52

此查询应识别两个表之间的数据类型不匹配。

SELECT 
  IST1.COLUMN_NAME as Table1_column
  ,IST1.DATA_TYPE as Table1_datatype
  ,IST2.COLUMN_NAME as Table2_column
  ,IST2.DATA_TYPE as Table2_datatype
FROM information_schema.columns IST1
INNER JOIN information_schema.columns IST2 
  ON IST1.ORDINAL_POSITION=IST2.ORDINAL_POSITION
WHERE IST1.table_name='Table1' AND IST1.TABLE_SCHEMA='dbo'
AND IST2.table_name='Table2' AND IST2.TABLE_SCHEMA='dbo'
AND IST1.DATA_TYPE <> IST2.DATA_TYPE

票数 2

Stack Overflow用户

发布于 2021-06-30 02:02:49

您可以这样做，这将返回数据类型不一致的表名和列名。但评论中的要点确实站得住脚：

应该在SELECT *

Having中显式定义列，而不是在单个表中使用SELECT *

Having 600列，这无疑是一个设计缺陷，应该加以解决。特别是当您想要UNION数据

时。

另外，如果您正在合并数据，我怀疑您需要的是UNION ALL，而不是UNION。我怀疑你的任何行在这里都是重复的，因为：

您有600个列，这意味着所有600个需要在两个单独的行上相同--
--两个表有不同的定义，这意味着两个表不能有重复的。

无论如何，一种方法是使用dm_exec_describe_first_result_set获取返回结果集的详细信息，然后从两个结果集比较位于相同序号位置的列的数据类型。

SELECT T1.[name] AS Table1Column,
       T1.system_type_name AS Table1Type,
       T2.[name] AS Table2Column,
       T2.system_type_name AS Table2Type
FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM Table1;',NULL,NULL) T1
     JOIN sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.Table2;',NULL,NULL) T2 ON T1.column_ordinal = T2.column_ordinal
WHERE T1.system_type_id <> T2.system_type_id;

票数 0

Stack Overflow用户

发布于 2021-06-30 07:19:43

对于SQL 2017及以上版本：

DECLARE @SomeString varchar(100) = 'ABC_DEF_GHI_jkl';
WITH T0 AS
(
SELECT 1 AS INDICE, 
       SUBSTRING(@SomeString, 1, 1) AS RAW_LETTER, 
       SUBSTRING(UPPER(@SomeString), 1, 1) AS UP_LETTER
UNION ALL
SELECT INDICE + 1, 
       SUBSTRING(@SomeString, INDICE + 1, 1) AS RAW_LETTER, 
       SUBSTRING(UPPER(@SomeString), INDICE + 1, 1)
FROM   T0
WHERE  INDICE < LEN(@SomeString)
)
SELECT STRING_AGG(RAW_LETTER, '') WITHIN GROUP (ORDER BY INDICE)
FROM   T0
WHERE  RAW_LETTER COLLATE Latin1_General_BIN = UP_LETTER;

对于2017年之前的Server：

WITH T0 AS
(
SELECT 1 AS INDICE, 
       SUBSTRING(@SomeString, 1, 1) AS RAW_LETTER, 
       SUBSTRING(UPPER(@SomeString), 1, 1) AS UP_LETTER
UNION ALL
SELECT INDICE + 1, 
       SUBSTRING(@SomeString, INDICE + 1, 1) AS RAW_LETTER, 
       SUBSTRING(UPPER(@SomeString), INDICE + 1, 1)
FROM   T0
WHERE  INDICE < LEN(@SomeString)
)
SELECT STUFF((SELECT '' + RAW_LETTER
              FROM   T0
              WHERE  RAW_LETTER COLLATE Latin1_General_BIN = UP_LETTER
              FOR XML PATH('')), 1, 0, '');