使用UNION ALL合并SQL临时表时出现转换错误的原因排查

问题描述 #

创建临时表后通过UNION ALL合并时出现类型转换错误，但直接合并原表却能正常执行。

报错的临时表合并代码 #

SELECT X, Y, Z
INTO #Tab1
FROM dbo.Cust

SELECT X, Y, Z
INTO #Tab2
FROM dbo.Cars

SELECT X, Y, Z
INTO #Tab3
FROM dbo.Colors

SELECT * FROM #Tab1
UNION ALL
SELECT * FROM #Tab2
UNION ALL
SELECT * FROM #Tab3

错误信息 #

Conversion failed when converting the nvarchar value .. to data type int

正常执行的原表合并代码 #

SELECT X, Y, Z
FROM dbo.Cust

UNION ALL

SELECT X, Y, Z
FROM dbo.Cars

UNION ALL

SELECT X, Y, Z
FROM dbo.Colors

问题原因 #

核心差异在于临时表的字段类型定义逻辑与直接UNION ALL原表的类型处理逻辑不同：

• 使用SELECT ... INTO创建临时表时，SQL Server会单独依据单个源表的字段类型来定义临时表的字段，每个临时表的字段类型完全继承自对应的源表。如果不同源表的同名字段看似类型一致，但实际存在隐性差异（比如一个是int，另一个是存储数字的nvarchar；或者字段长度、精度不同），临时表会保留这些差异。
• 直接对原表执行UNION ALL时，SQL Server会自动进行隐式类型兼容转换，将不同但可兼容的类型统一为更宽泛的类型（例如把int转换为nvarchar），因此不会触发转换错误。

举个典型场景：假设dbo.Cust的X字段是int，dbo.Cars的X字段是nvarchar(10)且存储内容多为数字。单独创建的#Tab1的X字段是int，#Tab2的X字段是nvarchar(10)。当UNION ALL这两个临时表时，SQL Server会优先以#Tab1的int类型为基准，尝试把#Tab2中X字段的nvarchar值转换为int，如果#Tab2的X字段存在非数字的字符串，就会触发转换失败。而直接合并原表时，SQL Server会把int类型的X转换为nvarchar，避免了转换错误。

验证与解决办法 #

验证方式 #

执行sp_help #Tab1、sp_help #Tab2查看临时表的字段类型，与原表的字段类型对比，即可发现类型差异。

解决办法 #

1. 手动定义临时表字段类型：避免依赖SELECT ... INTO的自动类型推断，手动创建临时表并指定统一的字段类型，确保所有临时表的字段类型完全一致：

CREATE TABLE #Tab1 (X nvarchar(50), Y int, Z datetime);
INSERT INTO #Tab1 (X, Y, Z) SELECT X, Y, Z FROM dbo.Cust;

CREATE TABLE #Tab2 (X nvarchar(50), Y int, Z datetime);
INSERT INTO #Tab2 (X, Y, Z) SELECT X, Y, Z FROM dbo.Cars;

CREATE TABLE #Tab3 (X nvarchar(50), Y int, Z datetime);
INSERT INTO #Tab3 (X, Y, Z) SELECT X, Y, Z FROM dbo.Colors;

2. 直接使用原表UNION ALL逻辑：如果临时表不是必须的，直接采用原表合并的代码即可避免问题。

内容的提问来源于stack exchange，提问作者mustafa00