Post reply

IF statement with Select INTO error

  • January 20, 2009 at 3:32 pm

    #205562

    sample script....

    declare @type char(1)

    set @type = 'A'

    if @type = 'A'

    begin

    select * into #temp where region = 'east'

    end

    else

    select * into #temp where region = 'west'

    error returned: Msg 2714, Level 16, State 1, Line 116

    There is already an object named '#temp' in the database.

    What is wrong with the script?

  • January 20, 2009 at 3:42 pm

    #930000

    You cannot use SELECT INTO twice within a script even if you wrap it in an IF. You need to create your temp table first, then use INSERT INTO inside your IF statement.

    I actually just wrote a topic about this a couple days ago, which you can see here:

    http://www.sqlservercentral.com/Forums/Topic639017-338-1.aspx

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • January 21, 2009 at 12:01 pm

    #930602

    thank you. i learn something new everyday!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply