2
IF @someVariable = 0
BEGIN
    SELECT * 
    INTO #TempTable
    FROM MyTable
    WHERE Category="Something"
END
ELSE
BEGIN
    SELECT * 
    INTO #TempTable
    FROM MyTable
    WHERE Category="SomethingElse"
END
DROP TABLE #TempTable

Comes up with "There is already an object named '#TempTable' in the database. Is there a way around this?

user1561572
  • 23
  • 1
  • 3
  • 1
    possible duplicate of [SQL Insert Into Temp Table in both If and Else Blocks](http://stackoverflow.com/questions/4155996/sql-insert-into-temp-table-in-both-if-and-else-blocks) – Andriy M Jul 29 '12 at 21:51
  • What is the reason you want to do this? – Thomas Jul 29 '12 at 22:11

2 Answers2

4
IF @someVariable = 0
BEGIN
   SELECT * 
   INTO #TempTable1
   FROM MyTable
   WHERE Category="Something"
   DROP TABLE #TempTable1
 END
ELSE
 BEGIN
   SELECT * 
   INTO #TempTable2
   FROM MyTable
   WHERE Category="SomethingElse"
   DROP TABLE #TempTable2
END

This will work, but depending on what else you may need the code to do, might not be the best solution; but in your example code it solves the problem.

Alternatively:

SELECT * into #TempTable from MyTable where (1=0)

IF @someVariable = 0
   INSERT INTO #TempTable SELECT * FROM MyTable WHERE Category="Something"
ELSE
   INSERT INTO #TempTable SELECT * FROM MyTable WHERE Category="OtherSomething"

DROP TABLE #TempTable
E.J. Brennan
  • 45,870
  • 7
  • 88
  • 116
2

Would a table variable fit your needs... I prefer these because of these scoping issues.

DECLARE @TempTable TABLE (
    -- Your definition
)

IF @someVariable = 0
BEGIN
    INSERT INTO @TempTable (...)
    SELECT ...
    FROM MyTable
    WHERE Category="Something"
END
ELSE
BEGIN
    INSERT INTO @TempTable (...)
    SELECT ...
    FROM MyTable
    WHERE Category="SomethingElse"
END

Or are you trying to prevent having to explicitly define the columns? Can you refactor to determine the condition beforehand:

DECLARE @Category varchar(100)
IF @someVariable = 0
BEGIN
    SET @Category = "Something"
END
ELSE
BEGIN
    SET @Category = "SomethingElse"
END

SELECT * 
INTO #TempTable
FROM MyTable
WHERE Category = @Category

DROP TABLE #TempTable
Glen Hughes
  • 4,712
  • 2
  • 20
  • 25
  • It doesn't have to be a table variable, though. The same approach would work with a temporary table as well. – Andriy M Jul 29 '12 at 22:00
  • So what are you trying to do... just get around explicitly declaring the column definitions? – Glen Hughes Jul 29 '12 at 22:03
  • Did you mean to address that question at the OP? Anyway, you probably forgot to remove `INTO @TempTable` after `SELECT ...` (in both queries). – Andriy M Jul 29 '12 at 22:11