Professional Web Applications Themes

Using IF and temporary tables problem - Microsoft SQL / MS SQL Server

I want to use a temporary table in an IF clause. For example: Declare Marker bit set Marker = 1 IF Marker =1 BEGIN select * into #temptable from Jobs where JobId= 1234 END ELSE BEGIN select * into #temptable from Jobs where JobId= 4321 END However this always gives the error message 'There is already an object named '#temptablenew' in the database.' If I remove the # everything is fine. How may I get this to work using temporary tables? Thanks, Adrian...

  1. #1

    Default Using IF and temporary tables problem

    I want to use a temporary table in an IF clause. For example:

    Declare Marker bit
    set Marker = 1

    IF Marker =1
    BEGIN

    select * into #temptable from Jobs where JobId= 1234

    END
    ELSE
    BEGIN

    select * into #temptable from Jobs where JobId= 4321

    END

    However this always gives the error message 'There is already an object
    named '#temptablenew' in the database.'

    If I remove the # everything is fine.

    How may I get this to work using temporary tables?

    Thanks,

    Adrian



    Adrian Guest

  2. #2

    Default Re: Using IF and temporary tables problem

    Try like this:

    Declare Marker bit
    set Marker = 1
    select * into #temptable from Jobs where 1=0
    IF Marker =1
    BEGIN
    INSERT INTO #temptable
    select * from Jobs where JobId= 1234
    END
    ELSE
    BEGIN
    INSERT INTO #temptable
    select * from Jobs where JobId= 4321
    END

    --
    Dejan Sarka, SQL Server MVP
    FAQ from Neil & others at: http://www.sqlserverfaq.com
    Please reply only to the newsgroups.
    PASS - the definitive, global community
    for SQL Server professionals - http://www.sqlpass.org

    "Adrian" <com> wrote in message
    news:phx.gbl... 


    Dejan Guest

  3. #3

    Default Re: Using IF and temporary tables problem

    first option, use CREATE TABLE #temptable (...) and then different insert
    .... select in each brach of the conditional execution.

    second one,

    --- extract metadata
    select * into #temptable from Jobs where 0=1

    IF Marker = 1
    BEGIN

    insert #temptable select * from Jobs where JobId= 1234

    END
    ELSE BEGIN

    insert #temptable select * from Jobs where JobId= 4321

    END

    HTH,
    </wqw>

    "Adrian" <com> wrote in message
    news:phx.gbl... 


    Vlad Guest

  4. #4

    Default Re: Using IF and temporary tables problem

    Dejan,

    Thanks very much. That resolves the problem.

    Adrian

    "Dejan Sarka" <si> wrote in
    message news:phx.gbl... 
    >
    >[/ref]


    Adrian Guest

  5. #5

    Default Re: Using IF and temporary tables problem

    Rather than using SELECT INTO here, you can avoid the error message by
    creating the temporary table explicitly. For example:
    ======
    DECLARE Marker INT
    CREATE TABLE #Temp (colA VARCHAR(20))
    BEGIN
    SET Marker = 2
    IF (Marker = 1)
    INSERT INTO #Temp SELECT au_id FROM authors
    ELSE
    INSERT INTO #Temp SELECT title_id FROM titles
    SELECT * FROM #Temp
    DROP TABLE #Temp
    END
    ======
    That should get you working.
    Note that temporary tables should be used with care. For your case, you can
    use the more efficient "table variable" data type. For example:
    ======
    DECLARE Marker INT
    DECLARE Temp TABLE (colA VARCHAR(20))
    BEGIN
    SET Marker = 1
    IF (Marker = 1)
    INSERT INTO Temp SELECT au_id FROM authors
    ELSE
    INSERT INTO Temp SELECT title_id FROM titles
    SELECT * FROM Temp
    END
    ======
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Adrian" <com> wrote in message
    news:phx.gbl... 


    SriSamp Guest

  6. #6

    Default Re: Using IF and temporary tables problem

    SriSamp,

    Thanks. I will look into the "table variable" data type.

    Adrian
    "SriSamp" <co.in> wrote in message
    news:phx.gbl... 
    can 
    >
    >[/ref]


    Adrian Guest

Similar Threads

  1. deleting using temporary tables
    By n00bie in forum MySQL
    Replies: 16
    Last Post: October 24th, 07:31 PM
  2. Cannot work with temporary tables
    By Nafiganado in forum Coldfusion Database Access
    Replies: 2
    Last Post: October 2nd, 03:25 PM
  3. Temporary tables privileges
    By Alejandro in forum PostgreSQL / PGSQL
    Replies: 3
    Last Post: March 14th, 05:36 PM
  4. Temporary Tables
    By Phil Jackson in forum IBM DB2
    Replies: 2
    Last Post: August 22nd, 05:05 AM
  5. global temporary tables
    By Blair Adamache in forum IBM DB2
    Replies: 0
    Last Post: July 31st, 07:44 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139