SQL 在 If 和 Else 块中插入临时表

SQL Insert Into Temp Table in both If and Else Blocks

提问by CuppM

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 ELSEblock INSERT INTOwith the error of:

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

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

数据库中已经有一个名为“#MyTestTable”的对象。

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

I could create the temp table before the IF/ELSEstatement and then just do INSERT SELECTstatements 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?

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

Thanks, Matt

谢谢，马特

采纳答案by Philip Kelley

The problem you're having is not that you are populating the temp table, but that you're trying to createthe 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

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

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.

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

回答by Gaspa79

Answering 8 years late, but I'm surprised nobody thought of:

回答晚了 8 年，但我很惊讶没有人想到：

select * into #MyTempTable from...
where 1=2

IF -- CONDITION HERE
insert into #MyTempTable select...
ELSE
insert into #MyTempTable select...

Simple, quick, and it works. No dynamic sql needed

简单，快速，而且有效。不需要动态sql

回答by John Hartsock

In the scenario you provide you could do this

在您提供的场景中，您可以执行此操作

DECLARE @Id int
SET @Id = 1

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

SELECT 
  CASE WHEN (@Id = 2) 
    THEN 'ABC' 
    ELSE 'XYZ' 
  END AS Letters
INTO #MyTestTable;

But otherwise you will need to create the table before the if statementlike this

但否则你将需要在if statement这样之前创建表

Create Table #MyTestTable (
  MyValue varchar(3)
)
IF (@Id = 2) BEGIN 
  Insert Into (MyValue)
  SELECT 'ABC' AS Letters;
END ELSE BEGIN
  Insert Into (MyValue)
  SELECT 'XYZ' AS Letters;
END

回答by Prateek Rai

Here is a solution which I use if temp table can't be created upfront and don't want to put core logic in dynamic SQL.

如果无法预先创建临时表并且不想将核心逻辑放在动态 SQL 中，我会使用以下解决方案。

IF 1 = 1 -- Replace with actual condition
BEGIN
    SELECT * INTO #tmp1 FROM dbo.Table1
END
ELSE
BEGIN
    SELECT * INTO #tmp2 FROM dbo.Table2
END

-- Inserting data into global temp table so sql server can't complain on not recognizing in a context
DECLARE @Command VARCHAR(MAX)
IF OBJECT_ID('tempdb..#tmp1') IS NOT NULL
BEGIN
    SET @Command = 'SELECT * INTO ##tmp FROM #tmp1'
END
ELSE
BEGIN
    SET @Command = 'SELECT * INTO ##tmp FROM #tmp2'
END

EXECUTE(@Command)
SELECT * INTO #tmpFinal FROM ##tmp -- Again passing data back to local temp table from global temp table to avoid seeing red mark

IF OBJECT_ID('tempdb..##tmp') IS NOT NULL DROP TABLE ##tmp
IF OBJECT_ID('tempdb..#tmp1') IS NOT NULL DROP TABLE #tmp1
IF OBJECT_ID('tempdb..#tmp2') IS NOT NULL DROP TABLE #tmp2

SELECT * FROM #tmpFinal

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

回答by Cyrage

I tried this:

我试过这个：

SELECT S1.* INTO #MytestTable
FROM 
(   SELECT 'ABC' AS Letters WHERE 1 = CASE @Id=2 THEN 1 ELSE 2 END
    UNION
    SELECT 'XYZ' AS Letters WHERE 1 = CASE @Id=1 THEN 1 ELSE 2 END
) AS S1

This solution is better if later you need to add columns to #MyTestTable, because otherwise you must physically drop it before you re-run your script, which is annoying iin test conditions.

如果稍后您需要向#MyTestTable 添加列，此解决方案会更好，因为否则您必须在重新运行脚本之前物理删除它，这在测试条件下很烦人。

回答by Rym

This is an old issue, but for anyone else coming here:

这是一个老问题，但对于来到这里的其他人：

The dynamic SQL answer given by user Philip Kelley does not work for local temp tables (#Mytemp). What you can do is create dynamic SQL to insert it into a global temp table (##MyTemp) which can later be dropped.

用户 Philip Kelley 给出的动态 SQL 答案不适用于本地临时表 ( #Mytemp)。您可以做的是创建动态 SQL 以将其插入到全局临时表 ( ##MyTemp) 中，该表稍后可以删除。

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

DROP ##MyTestTable

回答by Mohaddes Sokhangou

this code may help you

此代码可以帮助您

--creating temptable using columns of two existing tables
--you can create your temp table Using other methods

select top 0 VI.*,VU.FullName
into #mytemptable
from dbo.Items VI inner join
     dbo.Users as VU 
     on VU.Id=VI.Id

--insert your data base on your condition
if(i<2) --First Condition
begin
INSERT INTO #mytemptable
SELECT VI.*,VU.FullName 
from dbo.Items VI inner join
     dbo.Users as VU 
     on VU.Id=VI.Id
end
Else if(2<i) --Second Condition
begin
INSERT INTO #mytemptable
SELECT VI.*,VU.FullName 
from dbo.Items VI inner join
     dbo.Users as VU 
     on VU.Id=VI.Id
end

select * from #mytemptable --show result

drop table #mytemptable --drop table if its needed

this code works in sql server 2014 i don't know if it works in sql 2005 or not

此代码适用于 sql server 2014 我不知道它是否适用于 sql 2005

回答by Phil Sandler

You could drop the table before SELECTing INTO it in both cases., e.g.:

在这两种情况下，您都可以在 SELECTing INTO 之前删除该表。例如：

DECLARE @Id int 
SET @Id = 1  

IF (@Id = 2) BEGIN  
    IF OBJECT_ID('tempdb..#MyTestTable') IS NOT NULL DROP TABLE #MyTestTable
    SELECT 'ABC' AS Letters 
    INTO #MyTestTable; 
END ELSE BEGIN 
    IF OBJECT_ID('tempdb..#MyTestTable') IS NOT NULL DROP TABLE #MyTestTable 
    SELECT 'XYZ' AS Letters 
    INTO #MyTestTable; 
END 

Update After Comment:

评论后更新：

That's annoying.

这很烦人。

How about two separate temp tables? Then after the If/Else login, check for the existence of each one and if it exists, select into a third temp table? That may not perform great, but whether that matters or not depends on what you need this for.

两个单独的临时表怎么样？然后在 If/Else 登录后，检查每个是否存在，如果存在，则选择进入第三个临时表？这可能表现不佳，但这是否重要取决于您需要它做什么。