SQL 在 If 和 Else 块中插入临时表 [英] SQL Insert Into Temp Table in both If and Else Blocks

问题描述

我正在尝试根据 SQL 2005 中的条件结果填充临时表.无论哪种方式，临时表都将具有相同的结构，但将根据条件使用不同的查询进行填充.下面的简化示例脚本在 ELSE 块 INSERT INTO 的语法检查中失败，错误为:

I'm trying to populate a temp table based on the result of a condition in SQL 2005. The temp table will have the same structure either way, but will be populated using a different query depending on the condition. The simplified example script below fails in syntax checking of the ELSE block INSERT INTO with the error of:

已经有一个名为的对象数据库中的#MyTestTable".

There is already an object named '#MyTestTable' in the database.

DECLARE @Id int
SET @Id = 1

IF OBJECT_ID('tempdb..#MyTestTable') IS NOT NULL DROP TABLE #MyTestTable

IF (@Id = 2) BEGIN 
    SELECT 'ABC' AS Letters
    INTO #MyTestTable;
END ELSE BEGIN
    SELECT 'XYZ' AS Letters
    INTO #MyTestTable;
END

我可以在 IF/ELSE 语句之前创建临时表，然后在条件块中执行 INSERT SELECT 语句，但该表将有很多列和我试图提高效率.这是唯一的选择吗?或者有什么方法可以使这项工作?

I could create the temp table before the IF/ELSE statement and then just do INSERT SELECT statements in the conditional blocks, but the table will have lots of columns and I was trying to be efficient about it. Is that the only option? Or is there some way to make this work?

谢谢，马特

推荐答案

您遇到的问题不是您正在填充临时表，而是您正在尝试创建该表.SQL 解析您的脚本并发现您正试图在两个不同的地方创建它，因此引发错误.意识到执行路径"不可能同时命中两个 create 语句是不够聪明的.使用动态 SQL 将不起作用；我试过了

The problem you’re having is not that you are populating the temp table, but that you’re trying to create the table. SQL parses your script and finds that you are attempting to create it in two different places, and so raises an error. It is not clever enough to realize that the "execution path" cannot possibly hit both of the create statemements. Using dynamic SQL will not work; I tried

DECLARE @Command  varchar(500)

DECLARE @Id int 
SET @Id = 2

IF OBJECT_ID('tempdb..#MyTestTable') IS NOT NULL DROP TABLE #MyTestTable 

IF (@Id = 2) BEGIN  
    SET @Command = 'SELECT ''ABC'' AS Letters INTO #MyTestTable'
END ELSE BEGIN 
    SET @Command = 'SELECT ''XYZ'' AS Letters INTO #MyTestTable'
END 

EXECUTE (@Command)

select * from #MyTestTable

但临时表的持续时间与动态会话一样长.所以，唉，看来您必须先声明该表，然后再填充它.编写和支持的代码可能很笨拙，但它的执行效率足够高.

but the temp table only lasts as long as the dynamic session. So, alas, it looks like you’ll have to first declare the table and then populate it. Awkward code to write and support, perhaps, but it will perform efficiently enough.

这篇关于SQL 在 If 和 Else 块中插入临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！