Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update Temp table where the column names are unknown Expand / Collapse
Author
Message
Posted Thursday, December 26, 2013 12:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 26, 2013 7:19 PM
Points: 3, Visits: 5
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!


  Post Attachments 
tempTable.jpg (6 views, 42.47 KB)
Post #1526024
Posted Thursday, December 26, 2013 1:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:11 PM
Points: 12,905, Visits: 32,161
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1526026
Posted Thursday, December 26, 2013 1:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
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.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1526031
Posted Thursday, December 26, 2013 5:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 26, 2013 7:19 PM
Points: 3, Visits: 5
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.
Post #1526068
Posted Thursday, December 26, 2013 6:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 26, 2013 7:19 PM
Points: 3, Visits: 5
[quote]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'))


[quote]

'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.
Post #1526069
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse