SQL Insert Into Temp Table in both If and Else Blocks
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 statement
like this
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
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.
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
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.
Answering 8 years late, but I'm surprised nobody thought of:
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