I would like $1000.00 to go to the Flying A column and $500.00 go to the flying B column for the matching Mission #.
NOTE: Mission #s in Table1 are not unique; therefore, salaries for records with the same Mission # will be need to be summed in a temporary table before moving to Table2 (unless you have a better approach).
Thus Far I have the following code(using a temporary table to sum by Mission#):
DECLARE @ID INT DECLARE @Mission# NVARCHAR(100) DECLARE @Category NVARCHAR(100) DECLARE @Category2 NVARCHAR(100) DECLARE @Salary MONEY DECLARE @ColumnName NVARCHAR(1000) SET @ID = 1 --While @ID < 167921 --Begin SET @Mission# = (SELECT mission# FROM temporarytable WHERE id = @ID) SET @Category = (SELECT category FROM temporarytable WHERE id = @ID) SET @Category2 = (SELECT category2 FROM temporarytable WHERE id = @ID) SET @Salary = (SELECT salary FROM temporarytable WHERE id = @ID) SET @ColumnName = 'Base Salaries for ' + @Category + ' - ' + @Category2 EXECUTE ('update Table2 set @ColumnName = ''' + @Salary + ''' where [Mission#] = ''' + @Mission# + '''') --Set @ID = @ID + 1 --End
However, I can't seem to get the @ColumnName variable into the execute statement unless is is hard coded as the actual column name (eg. "Salaries for flying A - aircraft"). When I try and use @ColumnName I get a "must declare scalar variable "@ColumnName" error. If I mess around with the syntax a bit and use
Open in new window