Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    213
    Provided Answers: 2

    Answered: Why Can't I use the same table name when doing SELECT INTO within IF/ELSE?

    I ran into an interesting error using IF/ELSE. Basically, IF X then SELECT INTO table Y, ELSE [do some additional code] and SELECT INTO table Y. SQL throws a "table Y already exists" error. Since by nature of an IF/ELSE, both conditions can't happen, so why would it matter that the result of it inserts into the same table?

    Here is a simple example:
    Code:
    IF 1 = 1
    	SELECT 'foo' mycolumn INTO #test
    ELSE 
    	SELECT 'bar' mycolumn INTO #test

  2. Best Answer
    Posted by MCrowley

    "Because this is not compiled code.

    A query is passed through several phases before being executed:

    Syntax check
    Parsing
    Optimization
    Execution

    This is getting an error at the parsing phase, which is responsible for creating access trees, and enumerating all of the tables and columns that are to be used (and naturally checking their existence).

    Your best bet in this case is to create the table #test up front, and insert the data as a result of the IF."


  3. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,862
    Provided Answers: 17
    Because this is not compiled code.

    A query is passed through several phases before being executed:

    Syntax check
    Parsing
    Optimization
    Execution

    This is getting an error at the parsing phase, which is responsible for creating access trees, and enumerating all of the tables and columns that are to be used (and naturally checking their existence).

    Your best bet in this case is to create the table #test up front, and insert the data as a result of the IF.

  4. #3
    Join Date
    Nov 2004
    Posts
    1,428
    Provided Answers: 4
    Hi,

    I think SSMS does not recognise the IF-THEN-ELSE construct and throws an error when it spots the second SELECT INTO.

    I use the SELECT INTO construct only for ad hoc work, not for production code.
    Compare it with using SELECT *. Something that is not advised in production code neither.

    It is easy to avoid.

    Code:
    IF 1 = 1
    	INSERT INTO #test (mycolumn) SELECT 'foo' mycolumn From AnudderTable
    ELSE 
    	INSERT INTO #test (mycolumn) SELECT 'bar' mycolumn From YetAnudderTable
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #4
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    213
    Provided Answers: 2
    Quote Originally Posted by MCrowley View Post
    Because this is not compiled code.

    A query is passed through several phases before being executed:

    Syntax check
    Parsing
    Optimization
    Execution

    This is getting an error at the parsing phase, which is responsible for creating access trees, and enumerating all of the tables and columns that are to be used (and naturally checking their existence).

    Your best bet in this case is to create the table #test up front, and insert the data as a result of the IF.
    Makes sense. I did end up creating the table ahead of time and INSERTing into. Was just curious about why this was throwing an error. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •