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