Skip to content
Advertisement

Switch case with storing results into same table in SQL

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;

Advertisement

Answer

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.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement