SQL Server中如何使用IF ELSE条件填充临时表并避免对象已存在报错

报错原因 #

该错误是SQL Server的批处理编译机制导致的：语法解析阶段会校验整个批处理内的对象定义，不会判断IF分支的实际执行逻辑，只要同个批处理中出现了两次SELECT INTO #ONEYRCON的语句，就会判定临时表重复创建，直接抛出错误，和运行时的分支逻辑无关。

可行解决方案 #

你原来的写法不是唯一实现方式，以下两种是最常用的修复方案： #

方案1：先创建空临时表，再按条件插入（兼容性最好）

你可以先创建结构匹配的空临时表，再用INSERT INTO在不同分支中插入数据，完全规避重复创建的问题。如果不想手动声明字段类型，可以用TOP 0快速生成结构一致的空表：

IF OBJECT_ID('TEMPDB..#ONEYRCON') IS NOT NULL 
    DROP TABLE #ONEYRCON

-- 快速生成和查询结果结构一致的空临时表，不用手动写字段类型
SELECT TOP 0 tre.Dealermasterzone, tre.Dealermasterstate 
INTO #ONEYRCON
FROM [dbo].[T_RE_POLICY_TRANSACTION] tre 

IF @name IN ('asd', 'TVS')
BEGIN
    INSERT INTO #ONEYRCON (Dealermasterzone, Dealermasterstate)
    SELECT tre.Dealermasterzone, tre.Dealermasterstate 
    FROM [dbo].[T_RE_POLICY_TRANSACTION] tre 
    INNER JOIN #ONEYRBASEEXPIRY ony ON tre.ChassisNo = ony.chassisno  
    WHERE (CAST(InsPolicyCreatedDate AS date) BETWEEN @FirstDayCon AND @LastDayCon 
           AND BusinessType = @Name 
           AND (ony.PolExpDateSub <= CAST(tre.InsPolicyCreatedDate AS date))) 
       OR (@lastdayC < @lastdayM 
           AND CAST(InsPolicyCreatedDate AS date) BETWEEN @FirstDayCon AND @LastDayC 
           AND BusinessType = @Name)
    GROUP BY tre.Dealermasterzone, tre.Dealermasterstate
END
ELSE
BEGIN
    INSERT INTO #ONEYRCON (Dealermasterzone, Dealermasterstate)
    SELECT tre.Dealermasterzone, tre.Dealermasterstate  
    FROM [dbo].[T_RE_POLICY_TRANSACTION] tre 
    INNER JOIN #ONEYRBASEEXPIRY ony ON tre.ChassisNo = ony.chassisno 
                                    AND tre.currentyearIcName = ony.CurrentYearICName
    WHERE (CAST(InsPolicyCreatedDate AS date) BETWEEN @FirstDayCon AND @LastDayCon 
         AND BusinessType = @Name 
         AND (ony.PolExpDateSub <= CAST(tre.InsPolicyCreatedDate AS date))) 
     OR (@lastdayC < @lastdayM 
         AND CAST(InsPolicyCreatedDate AS date) BETWEEN @FirstDayCon AND @LastDayC 
         AND BusinessType = @Name)
    GROUP BY tre.Dealermasterzone,tre.Dealermasterstate
END

方案2：合并查询逻辑，单语句完成插入（代码更简洁）

因为两个分支的逻辑差异仅在于JOIN条件，你可以把分支判断直接写到JOIN条件中，不需要IF ELSE分支，一次SELECT INTO即可完成逻辑：

IF OBJECT_ID('TEMPDB..#ONEYRCON') IS NOT NULL 
    DROP TABLE #ONEYRCON

SELECT tre.Dealermasterzone, tre.Dealermasterstate 
INTO #ONEYRCON
FROM [dbo].[T_RE_POLICY_TRANSACTION] tre 
INNER JOIN #ONEYRBASEEXPIRY ony 
    ON tre.ChassisNo = ony.chassisno  
    AND (@name IN ('asd', 'TVS') OR tre.currentyearIcName = ony.CurrentYearICName)
WHERE (CAST(InsPolicyCreatedDate AS date) BETWEEN @FirstDayCon AND @LastDayCon 
       AND BusinessType = @Name 
       AND (ony.PolExpDateSub <= CAST(tre.InsPolicyCreatedDate AS date))) 
   OR (@lastdayC < @lastdayM 
       AND CAST(InsPolicyCreatedDate AS date) BETWEEN @FirstDayCon AND @LastDayC 
       AND BusinessType = @Name)
GROUP BY tre.Dealermasterzone, tre.Dealermasterstate

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