Update Temp table where the column names are unknown

  • In a stored procedure I dynamically create a temp table by selecting the name of Applications from a regular table. Then I add a date column and add the last 12 months. See attachment.

    So far so good. Now I want to update the data in columns by querying another regular table. Normally it would be something like:

    UPDATE ##TempTable

    SET [columName] = (SELECT SUM(columName)

    FROM RegularTable

    WHERE FORMAT(RegularTable.Date,'MM/yyyy') = FORMAT(##TempMonths.x,'MM/yyyy'))

    However, since I don't know what the name of the columns are at any given time, I need to do this dynamically.

    So my question is, how can I get the column names of a Temp table dynamically while doing an Update?

    Thanks!

  • info-531323 (12/26/2013)


    In a stored procedure I dynamically create a temp table by selecting the name of Applications from a regular table. Then I add a date column and add the last 12 months. See attachment.

    So far so good. Now I want to update the data in columns by querying another regular table. Normally it would be something like:

    UPDATE ##TempTable

    SET [columName] = (SELECT SUM(columName)

    FROM RegularTable

    WHERE FORMAT(RegularTable.Date,'MM/yyyy') = FORMAT(##TempMonths.x,'MM/yyyy'))

    However, since I don't know what the name of the columns are at any given time, I need to do this dynamically.

    So my question is, how can I get the column names of a Temp table dynamically while doing an Update?

    Thanks!

    the part that's missing is how you know the column gets updated;

    i assume that somewhere in your RegularTable, there must be a reference to the application now represented as a column in the ##TempTable;

    but your example doesn't show that;

    i'd bet some dynamic SQL will fix it, where you get teh column name based on the data.

    DECLARE @cmd VARCHAR(8000)

    SELECT 'UPDATE ##TempTable

    SET [' + ApplicationName +'] = (SELECT SUM(columName)

    FROM RegularTable

    WHERE FORMAT(RegularTable.Date,''MM/yyyy'') = FORMAT(##TempMonths.x,''MM/yyyy''))

    AND ApplicationName = ' + ApplicationName +' '

    PRINT @cmd

    EXEC (@cmd)

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • This will lead to some pretty miserable performance on top of it all... it makes for a 100% non-SARGable query. You could have a thousand index on the columns involved and the absolute best you'll ever get is an INDEX SCAN.

    WHERE FORMAT(RegularTable.Date,'MM/yyyy') = FORMAT(##TempMonths.x,'MM/yyyy'))

    I knew people would abuse the new FORMAT function as soon as I saw it. :sick:

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • DECLARE @cmd VARCHAR(8000)

    SELECT 'UPDATE ##TempTable

    SET [' + ApplicationName +'] = (SELECT SUM(columName)

    FROM RegularTable

    WHERE FORMAT(RegularTable.Date,''MM/yyyy'') = FORMAT(##TempMonths.x,''MM/yyyy''))

    AND ApplicationName = ' + ApplicationName +' '

    PRINT @cmd

    EXEC (@cmd)

    Thanks! I got it to work using your code's syntax.

  • Jeff Moden (12/26/2013)


    This will lead to some pretty miserable performance on top of it all... it makes for a 100% non-SARGable query. You could have a thousand index on the columns involved and the absolute best you'll ever get is an INDEX SCAN.

    WHERE FORMAT(RegularTable.Date,'MM/yyyy') = FORMAT(##TempMonths.x,'MM/yyyy'))

    'RegularTable' is updated once a month with a maximum of 10 new entries. That's 120 new lines a year, in twenty years it will have a total of 2,400 records. I don't think that even then performance will be an issue.

    That being said, I agree its probably not the best way, so if you have a suggestion on how I should approach that part of the query, please show me an example.

Viewing 5 posts - 1 through 4 (of 4 total)

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