0

Following is what my logic is supposed to do

IF @id = 1
BEGIN
  SELECT * INTO #abc from table1
END

IF @id = 2
BEGIN
  SELECT * INTO #abc frm table2
END

However, when I execute my statements I get the error saying

there is already an object named #abc..

Any suggestions to overcome this error please?

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
rock
  • 585
  • 3
  • 10
  • 26
  • 1
    I've cleaned up the question so it only deals with the reason you get the error message. The other parts of your process I believe deserve some real attention but you can deal with those separately. – Aaron Bertrand Jun 22 '12 at 16:38

2 Answers2

3

You can't. The parser doesn't understand your IF logic and it treats both SELECT INTO statements as things that will happen.

What you should do is:

IF @id = 1
BEGIN
SELECT * INTO #abc1 from table1
END

IF @id = 2
BEGIN
SELECT * INTO #abc2 frm table2
END

IF @id = 1
  SELECT * FROM #abc1;
ELSE
  SELECT * FROM #abc2;

After all, you need to know the different columns that are in the #temp table in order to do anything meaningful with it, right?

(Or avoid temp tables altogether.)

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • this works only till the point of SELECTING something INTO #abc1 or abc2.. as I mentioned in my question I have to put the result(meaning #abc1 or #abc2) INTO #GrandTotal.. your logic will give me two #GrandTotal which again will throw me the same error – rock Jun 20 '12 at 14:38
  • @ranjith I don't understand the problem. If #GrandTotal is always the result of `SUM(numeric_column), varchar_column` why does it have to change? The only thing you have to change is whether you insert the totals from #abc1 or #abc2. If the columns are different, and your query has to reflect that, I have absolutely no clue what you gain from naming the original tables the same. If you show your *whole* script, instead of just the part that causes the error you're getting, you might get more useful input into how to do it the right way instead of the way you've decided it has to be done. – Aaron Bertrand Jun 20 '12 at 14:41
  • I cannot add the entire script here because of the size limit.. is there any other way.. im sorry im new to stackoverflow.. – rock Jun 20 '12 at 14:57
  • You don't need to post your entire script. A small repro that represents what you're trying to do will suffice. It certainly sounds like you are doing multiple wrong things for various reasons... – Aaron Bertrand Jun 20 '12 at 14:59
0

Another possible solution:

CREATE TABLE #abc (
 --put schema here
)

IF @id = 1
BEGIN
  insert into #abc
  select * from table1
END

IF @id = 2
BEGIN
  insert into #abc
  select * from table2
END

select * from #abc

drop table #abc;

You should always use the column names instead of * because it's better in terms of performance.

And also, select * finds all the columns currently in a table, changes in the structure of a table such as adding, removing, or renaming columns automatically modify the results of select *. Listing columns individually gives you more precise control over the results.

aF.
  • 64,980
  • 43
  • 135
  • 198