Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Apr 2007
    Posts
    8

    Unanswered: Create #temp_table

    Hi,

    I need to create a #temp table, but only if it does not already exist.
    The code I'm trying is:

    Code:
    if (object_id('tempdb..#my_temp') is null) 
    begin 
        select 'null' 
        create table #my_temp ( id int )    
    end 
    else 
    begin 
        select 'not null'
    end
    but although this works the first time (and creates the temp table), it fails the second time with the complaint that the temp table already exists. This is despite the fact that the 'if' statement ought to prevent any attempt to create a duplicate.
    Can anyone help me with this?
    Thanks

  2. #2
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    hi,
    you can change the code as below. this will work

    if not exists (select 1 from tempdb..sysobjects where name='my_temp')
    begin
    select 'null'
    create table #my_temp ( id int )
    end
    else
    begin
    select 'not null'
    end
    Last edited by parangiri; 04-30-07 at 03:08.

  3. #3
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    hi,
    I think above might not work. u can try the below

    if not exists (select 1 from tempdb..sysobjects where name='my_temp')
    begin
    select 'null'
    create table #my_temp ( id int )
    end
    else
    begin
    select 'not null'
    end

  4. #4
    Join Date
    Apr 2007
    Posts
    8
    This does not seems to work.
    If the temp table already exists, I get the error message
    There is already an object named '#my_temp' in the database
    Any further help would be appreciated.

  5. #5
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    if not exists (select 1 from tempdb..sysobjects where name='my_temp')
    this condition will check whether the table my_temp is available in the tempdb or nor. if the table exists then else condition will be executed but u dont have any create in else part. how come u can get the error There is already an object named '#my_temp' in the database?. i think u have used if exists instead of if not exists

  6. #6
    Join Date
    Apr 2007
    Posts
    8
    Hi
    As a guess, it seems to me as though the sql engine scans through all of the sql and notices that if the 'create table' query were to be run, then it would cause a problem, so it does not allow it to be run at all.
    Have you tried your own query on a sysbase database? If so, are you confirming that it has worked after you have run it a few times? If so, could you tell me which version of Sybase you are using?
    I have tried your sugegstion, and my original sql, but each time I get the same error message (see above) about table already exists.

  7. #7
    Join Date
    Mar 2007
    Posts
    72
    Hi,
    In tempdb the object name is not exactly the name that you give. For example, if you say create table #dum, then in the sysobjects table the object name is not #dum but is a unique name, like, #dum_________00000950010548649, if another user tries to creates an object with name #dum then, he doesn't get an error but the object is created, how sybase does this is that it attaches a unique value to the name. This is a random value. So if you want to check the existence of the object you are better off using "select 1 from tempdb..sysobjects where name like '#dum%'; this would give a true condition, and your sql will loop to the else condition.

    hope this makes it clear.

    suda

  8. #8
    Join Date
    Apr 2007
    Posts
    8
    Hi.
    Thanks for clarifying what happens to the temp table name.#
    You are right that a guid is appended. That is why I do not want to use the condition
    where name='my_temp'
    or
    where name like 'my_temp%'
    since the latter would match ALL other temp tables with a similar name.

    The problem has nothing to do with the 'if' test.

    Please allow me to restate my problem, for clarity. Apologies for not doing this from the start:

    Suppose I already have temp table called #my_temp.
    Then, although this
    Code:
    if (1=2)
    begin
        create table #my_temp (id int)
    end
    sql should do nothing (because 1!=2), I find that it fails because 'There is already an object named '#my_temp' in the database'
    Is there anything I can do to resolve this?

  9. #9
    Join Date
    Mar 2007
    Posts
    72
    Hi,
    Now I get your problem, let me think and get back to you.

  10. #10
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    Hi,
    i feel, itis better to use global temporary tables (##) instead of (#). that might solve your problem. In global temp tables, the junk values does not get updated. For example u can change the code as below

    if not exists (select 1 from tempdb..sysobjects where name='##my_temp') begin
    select 'null'
    create table ##my_temp ( id int )
    end
    else
    begin
    select 'not null'
    end

    For further reference,
    http://forums.devx.com/archive/index.php/t-22716.html

  11. #11
    Join Date
    Apr 2007
    Posts
    8
    Hi,
    Thanks for the idea..
    Did you actually try this?
    It does not work for me in exactly the same way as before.
    The error now reads : "There is already an object named '##my_temp' in the database"
    Any further help is still appreciated.

  12. #12
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    Did u drop the #my_temp table which u have created before?.. i think that table is still there in tempdb?.

  13. #13
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    yes, there is no change even after using the global temporary table (##). This question is little bit tricky. The name column in sysobjects gives 40 bytes as a result. first 13 bytes (including #) is the table name. next 17 bytes gives the guid number. if the table name is only 5 chars. then other 8 chars till 13th byte is filled with _..

    EX:
    select name from sysobjects
    name
    #myth________12345678901234567

    To maintain uniquenes, they are forming the name in such a manner.

  14. #14
    Join Date
    Apr 2007
    Posts
    8
    Isn't the 'if' statement totally beside the point?
    Is there something wrong with the test I wrote originally, namely
    if (object_id('tempdb..#my_temp') is null)
    My question is not about the 'if' test itself, it is to do with the fact that the body of the 'if' statement prevents the sql from being run, even when the condition being tested is false.
    Hence my restatement of the question:
    Suppose I already have temp table called #my_temp.
    Then, although this
    Code:
    if (1=2)
    begin
        create table #my_temp (id int)
    end
    sql should do nothing (because 1!=2), whereas in fact, I see it fails

  15. #15
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    Hi,
    i think u can include the below code in the procedure and try. This might work. i am scanning through all the names in sysobjects and comparing with the temporary table.

    declare @tablename as varchar(40)
    declare @subtable as varchar(13)
    declare @flag int
    select @flag = 0

    declare name_crsr cursor for
    select name from tempdb..sysobjects where name like '#%'
    for read only

    open name_crsr

    fetch name_crsr into @tablename

    while (@@sqlstatus = 0)
    begin
    select @subtable = substring(@tablename, 1 , 13)
    if @subtable = '#my_temp_____' -- i am appending with _ to get 13 digits
    select @flag = 1

    fetch name_crsr into @tablename
    end

    close name_crsr

    deallocate cursor name_crsr

    if @flag = 0
    begin
    select 'null'
    create table #my_temp ( id int )
    end
    else
    begin
    select 'not null'
    end

Posting Permissions

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