Dropping and creating tables.

  • -- EXEC SP_DROP ##XXX        -- For older versions of SQLserver
    DROP TABLE IF EXISTS ##XXX
    select top 11 * INTO ##XXX from information_schema.columns

    -- EXEC SP_DROP ##XXX -- For older versions of SQLserver
    DROP TABLE IF EXISTS ##XXX
    select top 11 * INTO ##XXX from information_schema.columns

    The code above is an example of what I am trying to do in a stored procedure. With 'normal' tables this works ok. With a hashhash table this gives :

    Msg 2714, Level 16, State 1, Line 21

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

    What would be a good work around?

    Thanks for your time and attention,

    Ben

    (I can come up with some work arounds, like use different table names, or not use hashhash tables, but I am looking for a more elegant solution. In coding I very often create tables (##,# and 'normal') and drop and create them at 'will'. The SP_drop does effectively the same is the DROP IF EXISTS and was and is used a lot because it works in older versions as well).

     

    • This topic was modified 3 years, 2 months ago by  ben.brugman.
  • I do not know the answer, but out of interest, under what circumstances do you choose to use global temp tables in preference to 'normal' session-scoped temp tables?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • The problem is the parser here. You are attempting to create the same object twice in the same batch, and thus the parser sees the second attempt to create the same object (##XXX) and generates an error. It doesn't matter that the object will most certainly be dropped before hand, the fact is that you try to create it again. Also note that NONE of the batch is run; the parser sees the "error" and doesn't even attempt to execute the batch.

    The "simple" solution would be to divide into 2 batches. In SSMS/ADS/sqlcmd that would be by using GO:

    DROP TABLE IF EXISTS ##XXX
    select top 11 * INTO ##XXX from information_schema.columns
    GO
    DROP TABLE IF EXISTS ##XXX
    select top 11 * INTO ##XXX from information_schema.columns

    This problem can actually be reproduced other ways, take for example the following:

    DECLARE @I int = 1;

    IF @I = 1
    CREATE TABLE #X (I int);
    ELSE
    CREATE TABLE #X (C char(1));

    This batch will produce the error below, despite that both statements can never be run  in the same batch:

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

    Another method of getting around this, when not using temporary objects, is to use statements where their compilation will be deferred:

    DECLARE @I int = 1;

    IF @I = 1
    EXEC sys.sp_executesql N'CREATE TABLE dbo.X (I int);';
    ELSE
    EXEC sys.sp_executesql N'CREATE TABLE dbo.X (C char(1));';
    GO

    DROP TABLE dbo.X;

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Perhaps a synonym would work for you here.

    Why do the created tables have to have the same name?  Presumably it's because some common code needs to run against the tables?  If so, perhaps the code could work against a synonym and the actual table name wouldn't matter.

    DROP TABLE IF EXISTS #XXX1
    select top (11) * INTO #XXX1 from sys.tables

    create synonym xxx$ for #xxx1;
    select * from xxx$;

    DROP TABLE IF EXISTS #XXX2
    select top (11) * INTO #XXX2 from sys.views

    drop synonym xxx$;
    create synonym xxx$ for #xxx2;
    select * from xxx$

     

     

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Phil Parkin wrote:

    I do not know the answer, but out of interest, under what circumstances do you choose to use global temp tables in preference to 'normal' session-scoped temp tables?

    I like to use global temp tables. During developing/testing/debugging of stored procedures, whenever I run into a 'not completed' (or erroneous) piece of code, I can inspect or continue from another window.

    Yes the disadvantage is that you can not make re-entrant code, but certainly during development I prefer that that is not possible, with local temp tables I sometimes test on a 'wrong' table.

    For production code I have to do a clean-up (removing lots of comments and some test/debug code), depending on the function and use of the script I sometimes substitute the global temporary tables.

    At the moment I am building two compare routines.

    Main features are:

    • Compare two tables. (Same database, different database, SQLserver, SQLAnywhere, all combinations).

      Content compare, results into OLD and NEW rows.

    • Can compare two databases (SQLserver, SQLAnywhere, combinations).
    • Can compare counts (taken from the system tables (SQLAnywhere and SQLServer).
    • Can compare meta data, tables and columns.
    • Results to tables or results to display or into tables in a target database. (With a 'free' choice of the target database an a prefix for the tablenames).
    • Compares also work without the target database, so the tempfiles (or files in the tempdb) are needed.

      Code can be rerun, so tables can be 'reused'.

    Reason:

    We are (slowly) moving from SQLAnywhere to SQLserver.

    • Testing for same results.
    • Testing for what is happening.
    • Finding alternatives for the SQLAnywhere logging (and replay).

    Customers want to see prove that migration to SQLserver had gone correct and want to have prove that actions do give the same result. But also build for future functions (compare between versions etc., debugging.).

    Ben

  • -- EXEC SP_DROP XXX        -- For older versions of SQLserver
    DROP TABLE IF EXISTS XXX
    select top 11 * INTO XXX from information_schema.columns

    -- EXEC SP_DROP XXX -- For older versions of SQLserver
    DROP TABLE IF EXISTS XXX
    select top 11 * INTO XXX from information_schema.columns

    The above code does not give the mentioned error. Although following the same reasoning (the same object twice in the same [stored procedre]) the parser should (???) give a similar error. I am curious about the difference (XXX or ##XXX) to the parser.

    Thom A wrote:

    The problem is the parser here. You are attempting to create the same object twice in the same batch

    Thom A wrote:

    The "simple" solution would be to divide into 2 batches. In SSMS/ADS/sqlcmd that would be by using GO:

    DROP TABLE IF EXISTS ##XXX
    select top 11 * INTO ##XXX from information_schema.columns
    GO
    DROP TABLE IF EXISTS ##XXX
    select top 11 * INTO ##XXX from information_schema.columns

    Problem: Stored procedure and in a script the @parameters do not 'survive'

     

     

    Thom A wrote:

    This problem can actually be reproduced other ways, take for example the following:

    DECLARE @I int = 1;

    IF @I = 1
    CREATE TABLE #X (I int);
    ELSE
    CREATE TABLE #X (C char(1));

    Yes similar to my 'original' problem conditional creation of a table.

    Thom A wrote:

    Another method of getting around this, when not using temporary objects, is to use statements where their compilation will be deferred:

    To circumvent the problem I now use indeed a dynamic structure, but for 'fixed' table names I do not consider this elegant.

    Thanks for your time and attention,

    Ben

    • This reply was modified 3 years, 2 months ago by  ben.brugman.
  • ScottPletcher wrote:

    Perhaps a synonym would work for you here.

    Why do the created tables have to have the same name?  Presumably it's because some common code needs to run against the tables?  If so, perhaps the code could work against a synonym and the actual table name wouldn't matter. 

    The code picks up data from two databases and works for SQLAnywhere (A) and for SQLserver databases (B). The code for SQLAnywhere is different then from SQLserver.

    I can compare A<-->A, A<-->B, B<-->A and B<--B

    the data for the first part is stored in a table, the data for the second part is stored in a table. But two sets of code are used for part A (one for SQLanywhere, one for SQLserver), same for second part. After the data had been gatherered, both result tables are combined. If I use different tablesnames for each 'collection', I would need 8 tablenames and 4 differenct script for combining the tables. Now I only have two tables with source information which gets combined in a single script. This is sufficient reason for me to have tables with the same name.

    Thanks for your time and attention,

    Response to your anwsers has been spread over time, first because of my work second, because there was (again) a pressconference from our (cycling) prime minister. (An extention of our lockdown).

    Ben

     

     

  • ben.brugman wrote:

    Problem: Stored procedure and in a script the @parameters do not 'survive'

    Well no, but there were no parameters in your script, nor was it provided as a Procedure; I wouldn't have recommended it as a "fix" if that were the case. 🙂

    ben.brugman wrote:

    Yes similar to my 'original' problem conditional creation of a table.

    That was my point.

    ben.brugman wrote:

    To circumvent the problem I now use indeed a dynamic structure, but for 'fixed' table names I do not consider this elegant.

    It honestly looks like you have a bit of an XY Problem, if I am honest. Why are you trying to create the same object in different places in the same batch anyway? I assume it'll have the same definition, so why not create the table first, and the later use a INSERT INTO rather than SELECT ... INTO? Then you don't have 2 attempts to create the same table in the same batch, and the error doesn't occur. If it isn't going to have the same definition, then giving it the same name, in my opinion, is a bad idea, as it can easily cause other problems if you have references to the object later, and it's not the right "version" of it.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thom A wrote:

    Well no, but there were no parameters in your script, nor was it provided as a Procedure; I wouldn't have recommended it as a "fix" if that were the case. 🙂

    I apologise that I wasn't clear, I did mention that it was code from a stored procedure and the example was not very 'functional', it was just an illustration where the code was kept to a minimum

    ben.brugman wrote:

    Why are you trying to create the same object in different places in the same batch anyway?

    In a previous post I tried to explain this. And because my code is often used as a tool where the future use of the tool is not defined I often take the liberty to not assuming the definition of the tables the code has to work on.

    Thanks for your time and attention,

    For the moment I have choosen to use dynamic code,

    Ben

     

     

    • This reply was modified 3 years, 2 months ago by  ben.brugman.

Viewing 9 posts - 1 through 8 (of 8 total)

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