none
Scope of temp table in dynamic sql

    Question

  • User uploads the data and it is stored in a dynamically created table (say UserData). Please note that table (UserData) structure is not fixed. We have very big processing logic embedded in a SP and it starts like it.

                 Declare @sql varchar(1000)

    /*

    @ UserTblName is the input sp parameter and it holds the value UserData for this scenario

    */

                SET @sql =  ‘select * into #UserData from  ‘ + @UserTblName

                Exec(@sql)

               

                Select * from #UserData

     

    The above statement is giving the below error.  All the calculation is based on the #UserData table.

     

    Msg 208, Level 16, State 0, Line 5

    Invalid object name '#UserData'.


    I'm aware that if we create the temp table upfront and use it in dynamic sql statement will work. But the constraint is that we are not sure about the table structure upfront. So creating an temp table upfront is not an option.           

                

    Thank you in advance for the hint to handle this scenario.

     

    With regards

    Ganesh

    Tuesday, March 03, 2009 7:59 PM

Answers

  • Ganesh,

    This might not be the best sollution for a normal approach, however I believe it fits your needs, because you have a very specific sittuation where you don't know the table structure upfront.

    Try it, and then you can modify accordingly as you encounter unhandled types...

    Please let me know what you think...

    DECLARE @TableName  varchar(256),  
            @ColName varchar(256),   
            @TypeName varchar(256),   
            @TypeLength smallint,   
            @TypeCollation varchar(256)  
     
    SET @TableName = 'YourTableName' 
     
    CREATE TABLE #TMPTABLE(DUMMYFIELD_ CHAR(1))  
     
    SELECT * FROM #TMPTABLE  
     
    DECLARE SYSOBJECTS_CURSOR CURSOR FOR 
    SELECT SYSCOLUMNS.NAME, SYSTYPES.NAME, SYSCOLUMNS.LENGTH, SYSTYPES.COLLATION  
    FROM SYSCOLUMNS   
     JOIN SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID  
     JOIN SYSTYPES ON SYSTYPES.XTYPE = SYSCOLUMNS.XTYPE  
    WHERE SYSOBJECTS.NAME = @TableName  
     
    OPEN SYSOBJECTS_CURSOR  
    FETCH NEXT FROM SYSOBJECTS_CURSOR INTO @ColName, @TypeName, @TypeLength, @TypeCollation  
     
    WHILE @@FETCH_STATUS = 0  
    BEGIN 
      -- This is done to handle string types (Probably other types, such as decimal   
      -- also need special attention)  
      IF (@TypeCollation IS NOT NULL)  
      BEGIN 
        SET @TypeName = @TypeName + '(' + CAST(@TypeLength AS VARCHAR) + ')' 
      END 
     
      DECLARE @sql VARCHAR(1000)  
      SET @sql = 'ALTER TABLE #TMPTABLE ADD ' + @ColName + ' ' + @TypeName + ' NULL' 
      EXEC(@sql)  
     
      FETCH NEXT FROM SYSOBJECTS_CURSOR INTO @ColName, @TypeName, @TypeLength, @TypeCollation  
    END 
     
    CLOSE SYSOBJECTS_CURSOR  
    DEALLOCATE SYSOBJECTS_CURSOR  
     
    ALTER TABLE #TMPTABLE DROP COLUMN DUMMYFIELD_  
     
    -- Do whatever you want here....and then drop the table  
     
    DROP TABLE #TMPTABLE 

    I hope this helps, Rafael Medeiros
    • Marked as answer by SGKumar Wednesday, March 04, 2009 3:09 PM
    Tuesday, March 03, 2009 10:46 PM

All replies

  • You might consider using a ## table. This is a global temporary table, so a parallel call to your code will fail.

    Other option: 

    Create an unique name for the temptable: 

    Declare @sql varchar(1000)

    /*

    @ UserTblName is the input sp parameter and it holds the value UserData for this scenario

    */

    DECLARE @TempTable Varchar(255)
    SET @TempTable = QUOTENAME('##' + CONVERT(Varchar(36), newid()))

    SET @sql =  'SELECT * INTO ' + @TempTable + ' FROM ' + @UserTblName
    Exec(@sql)


    SET @Sql = 'Select * from ' + @TempTable
    EXEC (@Sql)

    SET @Sql = 'DROP TABLE ' + @TempTable
    EXEC (@Sql)


    Ewald - Please remember to mark the replies as answers if they help.
    Tuesday, March 03, 2009 8:32 PM
  • Is there any other way to get @UserTblName data into  #UserData ?
    Tuesday, March 03, 2009 9:01 PM
  • Hi,

    Just create your #UserData table with some sample structure, before you try your script, and it won't complain.

    Regards,
    John

    Tuesday, March 03, 2009 9:01 PM
  • Dear John,
      
       As I have mentioned, I will not know the table structure upfront. 

    With regards
    Ganesh
    Tuesday, March 03, 2009 9:03 PM
  • The other option generates an unique ## temp table, so you can work concurrently.
    Ewald - Please remember to mark the replies as answers if they help.
    Tuesday, March 03, 2009 9:06 PM
  • SGKumar said:

    Dear John,

      
       As I have mentioned, I will not know the table structure upfront. 

    With regards
    Ganesh



    Hi Ganesh,

    This is why i suggested that you create it with a sample structure (whatever, doesn't matter).
    If the temp table exists, then you will be able to create your SP.
    Try it and let me know,

    Regards
    Tuesday, March 03, 2009 9:10 PM
  • I've tried the below and was not getting the desired result.

    select  column1 into #userdata from [user table] -- worked successfully

    select * from #userdata  -- result has one column

    1. SET @sql =  ‘select * into #UserData from  ‘ + @UserTblName

     2.    Exec(@sql)

    The above two sql worked successfully. The source table  @UserTblName has 10 columns (for example)


    Select * from #userdata  -- result has one column instead of 10 columns


    with regards

    Ganesh

    Tuesday, March 03, 2009 10:06 PM
  • Ganesh,

    This might not be the best sollution for a normal approach, however I believe it fits your needs, because you have a very specific sittuation where you don't know the table structure upfront.

    Try it, and then you can modify accordingly as you encounter unhandled types...

    Please let me know what you think...

    DECLARE @TableName  varchar(256),  
            @ColName varchar(256),   
            @TypeName varchar(256),   
            @TypeLength smallint,   
            @TypeCollation varchar(256)  
     
    SET @TableName = 'YourTableName' 
     
    CREATE TABLE #TMPTABLE(DUMMYFIELD_ CHAR(1))  
     
    SELECT * FROM #TMPTABLE  
     
    DECLARE SYSOBJECTS_CURSOR CURSOR FOR 
    SELECT SYSCOLUMNS.NAME, SYSTYPES.NAME, SYSCOLUMNS.LENGTH, SYSTYPES.COLLATION  
    FROM SYSCOLUMNS   
     JOIN SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID  
     JOIN SYSTYPES ON SYSTYPES.XTYPE = SYSCOLUMNS.XTYPE  
    WHERE SYSOBJECTS.NAME = @TableName  
     
    OPEN SYSOBJECTS_CURSOR  
    FETCH NEXT FROM SYSOBJECTS_CURSOR INTO @ColName, @TypeName, @TypeLength, @TypeCollation  
     
    WHILE @@FETCH_STATUS = 0  
    BEGIN 
      -- This is done to handle string types (Probably other types, such as decimal   
      -- also need special attention)  
      IF (@TypeCollation IS NOT NULL)  
      BEGIN 
        SET @TypeName = @TypeName + '(' + CAST(@TypeLength AS VARCHAR) + ')' 
      END 
     
      DECLARE @sql VARCHAR(1000)  
      SET @sql = 'ALTER TABLE #TMPTABLE ADD ' + @ColName + ' ' + @TypeName + ' NULL' 
      EXEC(@sql)  
     
      FETCH NEXT FROM SYSOBJECTS_CURSOR INTO @ColName, @TypeName, @TypeLength, @TypeCollation  
    END 
     
    CLOSE SYSOBJECTS_CURSOR  
    DEALLOCATE SYSOBJECTS_CURSOR  
     
    ALTER TABLE #TMPTABLE DROP COLUMN DUMMYFIELD_  
     
    -- Do whatever you want here....and then drop the table  
     
    DROP TABLE #TMPTABLE 

    I hope this helps, Rafael Medeiros
    • Marked as answer by SGKumar Wednesday, March 04, 2009 3:09 PM
    Tuesday, March 03, 2009 10:46 PM
  • Thank a lot Rafael Medeiros. This will help.
    Wednesday, March 04, 2009 3:09 PM