SQL临时表脚本首次运行正常第二次报错：列名或值数量与表定义不匹配

问题根因 #

你遇到的报错本质不是临时表没有被成功删除，而是SQL Server的延迟名称解析特性导致的批处理预编译冲突：

1. 首次运行脚本时，会话中不存在#TMP，编译阶段不会提前检查后续INSERT语句的字段匹配性，执行顺序为：执行DROP IF EXISTS（无操作）→ 执行SELECT INTO按#compare1的结构创建#TMP → 执行INSERT，只要#compare1和#compare2结构一致即可正常运行。
2. 同会话第二次运行时，编译整个批处理的阶段，会话中还残留上一次执行生成的#TMP元数据（哪怕你脚本开头写了DROP IF EXISTS，编译阶段不会先执行DDL语句），编译器会直接用旧#TMP的结构去校验你后续的INSERT语句，如果有字段不匹配就会提前抛错，根本不会走到你写的DROP逻辑那一步。

解决方案 #

方案1：用GO拆分成独立批处理（最省事） #

在DROP IF EXISTS语句后面加GO关键字，把删除临时表的逻辑和后续建表、插入逻辑拆成两个独立批处理，这样编译第二个批的时候，第一个批的DROP已经执行完成，旧#TMP已经被删除，不会有旧元数据干扰：

-- create table of differences
drop table if exists #TMP;
GO -- 新增此行拆分批处理

select A.*,'table A' TABLE_NAME INTO #TMP from  #compare1 a
full outer join #compare2 b on a.hash = b.hash
where ( a.hash is null or b.hash is null );

INSERT INTO #TMP
select B.*,'table B' from  #compare1 a
full outer join #compare2 b on a.hash = b.hash
where ( a.hash is null or b.hash is null );

注意：如果你的脚本里有变量需要跨批使用，该方案不适用，可选择下方其他方案。

方案2：显式定义临时表结构，避免SELECT INTO隐式建表 #

提前用CREATE TABLE明确#TMP的结构，消除隐式建表的结构不确定性，同时也不会触发编译阶段的元数据冲突：

drop table if exists #TMP;
-- 显式定义结构，需和#compare1、#compare2的字段顺序、类型完全匹配，最后加TABLE_NAME字段
CREATE TABLE #TMP (
    -- 此处按#compare1的字段依次声明，示例：id INT, content VARCHAR(100), ...,
    TABLE_NAME VARCHAR(10)
);

INSERT INTO #TMP
select A.*,'table A' from  #compare1 a
full outer join #compare2 b on a.hash = b.hash
where ( a.hash is null or b.hash is null );

INSERT INTO #TMP
select B.*,'table B' from  #compare1 a
full outer join #compare2 b on a.hash = b.hash
where ( a.hash is null or b.hash is null );

该方案的额外优势是如果后续#compare1/#compare2结构发生变化，会直接在建表阶段报错，不会出现隐式结构不匹配的隐性bug。

方案3：用动态SQL执行建表+插入逻辑 #

把后续的建表插入逻辑包在动态SQL中，动态SQL只会在执行阶段才编译，这时候DROP已经执行完成，旧临时表已经被清理：

drop table if exists #TMP;

EXEC sp_executesql N'
select A.*,''table A'' TABLE_NAME INTO #TMP from  #compare1 a
full outer join #compare2 b on a.hash = b.hash
where ( a.hash is null or b.hash is null );

INSERT INTO #TMP
select B.*,''table B'' from  #compare1 a
full outer join #compare2 b on a.hash = b.hash
where ( a.hash is null or b.hash is null );
'

注意：临时表在动态SQL内部创建的话，作用域仅局限于动态SQL内部，如果后续还要在外部使用#TMP，该方案不适用。

额外检查项 #

无论使用哪种方案，都要提前确认#compare1和#compare2的列数、列顺序、对应列的数据类型完全一致，否则即使解决了编译冲突，插入阶段仍会报字段不匹配的错误。

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