December 26, 2013 at 12:47 pm
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!
December 26, 2013 at 1:02 pm
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
December 26, 2013 at 1:19 pm
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
Change is inevitable... Change for the better is not.
December 26, 2013 at 5:57 pm
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.
December 26, 2013 at 6:02 pm
Jeff Moden (12/26/2013)
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