Switch case with storing results into same table in SQL

Question

I want to store the results into table with same name as per the condition. How to achieve the same ? Following is the code: While executing it throws error that #a already exists.

IF @Input ='1'
        BEGIN
                drop #a
                SELECT *
                INTO #a
                FROM table1
        END;

    ELSE IF @Input ='2'
        BEGIN
                drop #a
                SELECT *
                INTO #a
                FROM table2
        END;

What version of SQL Server are you using? – Radu Gheorghiu Jan 31 '20 at 08:57 — Radu Gheorghiu, Jan 31 '20 at 08:57

score 0 · Answer 1 · answered Jan 31 '20 at 08:33

0

try this

   if object_id('tempdb..#a') is not null 
   drop table #a

    IF @Input ='1'
            BEGIN
                    SELECT *
                    INTO #a
                    FROM table1
            END;
        ELSE IF @Input ='2'
            BEGIN
                    SELECT *
                    INTO #a
                    FROM table2
            END;

answered Jan 31 '20 at 08:33

Dgan

10,077
1
29
51

What is the difference? – Jan 31 '20 at 08:36

Sebastian Brosch · Accepted Answer · 2020-01-31T09:56:20.697

You can use this solution using a global temporary table (maybe not the best / safest solution). The statements get executed with EXECUTE:

DECLARE @Input VARCHAR(20) = '1'

IF OBJECT_ID('tempdb..##a') IS NOT NULL
  BEGIN
    DROP TABLE ##a 
  END

IF @Input = '1'
    EXEC ('SELECT * INTO ##a FROM table1;') 
ELSE IF @Input = '2'
    EXEC ('SELECT * INTO ##a FROM table2;')

-- you can implement steps here to create a local temporary table.
-- see: https://stackoverflow.com/questions/9534990/tsql-select-into-temp-table-from-dynamic-sql

SELECT * FROM ##a

Also have a look at this question: TSQL select into Temp table from dynamic sql. There is also described how you can get the data as local temporary table in two different ways (using a global temporary table or a view).

The problem using the EXECUTE function is leaving the scope.

Switch case with storing results into same table in SQL

2 Answers2