asked on Use Variable to Identify a Column Name for update
Hi Experts,
I have data in Table1:
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#):
Am I missing something? Is there a better approach?
I have data in Table1:
id | mission # | category | category2| salary
1 | 12345 | flying A | aircraft| $1000.00
2 | 54321 | flying B | aircraft| $500.00
mission # | "Salaries for flying A - aircraft" | "Salaries for flying B - aircraft"|
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
EXECUTE ('update Table2 set ' + @ColumnName + ' = ''' + @Salary +
''' where [Mission#] = ''' + @Mission# + '''')
Am I missing something? Is there a better approach?
Microsoft SQL Server
Last Comment
-Polak
ASKER
With that I get an Incorrect syntax near the keyword 'where' error.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks Daniel, the Dynamic SQL syntax is pretty confusing. Both your methods work wonderfully.
Do any tips on speeding up this loop? I'm running SQL server locally on my laptop and this executes at a speed of about .17 seconds per record and will take about 8 Hours to complete..? Most of the time is being spent on Index Seek and RID Lookup (Heat).
Do any tips on speeding up this loop? I'm running SQL server locally on my laptop and this executes at a speed of about .17 seconds per record and will take about 8 Hours to complete..? Most of the time is being spent on Index Seek and RID Lookup (Heat).
If I understand correctly, you don't need a temp table.
INSERT INTO table2 ( mission#, [Salaries for flying A - aircraft], [Salaries for flying B - aircraft] )
SELECT mission#,
SUM(CASE WHEN category = 'flying A' /*AND category2 = 'aircraft'*/ THEN salary ELSE 0 END) AS [Salaries for flying A - aircraft],
SUM(CASE WHEN category = 'flying B' /*AND category2 = 'aircraft'*/ THEN salary ELSE 0 END) AS [Salaries for flying B - aircraft]
FROM table1
WHERE
category IN ('flying A', 'flying B') /*AND category2 = 'aircraft'*/
GROUP BY mission#
INSERT INTO table2 ( mission#, [Salaries for flying A - aircraft], [Salaries for flying B - aircraft] )
SELECT mission#,
SUM(CASE WHEN category = 'flying A' /*AND category2 = 'aircraft'*/ THEN salary ELSE 0 END) AS [Salaries for flying A - aircraft],
SUM(CASE WHEN category = 'flying B' /*AND category2 = 'aircraft'*/ THEN salary ELSE 0 END) AS [Salaries for flying B - aircraft]
FROM table1
WHERE
category IN ('flying A', 'flying B') /*AND category2 = 'aircraft'*/
GROUP BY mission#
ASKER
@Scott, if i understand you correctly. Yes, transposing the data would likely run quicker; however, what's not completely clear in my example (my fault) is that Table2 already has mission # data in it and what needs to be brought over from Table1 is the salary information depending on category and category2.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Not sure I can help with the speed issue. A couple things that might help, This:
Can easily be:
I doubt that'll save much time, but typing, yes.
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)
Can easily be:
SELECT
@Mission# = mission#,
@Category = category,
@Category2 = category2,
@Salary = salary
FROM temporarytable
WHERE id = @ID
I doubt that'll save much time, but typing, yes.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for the help gents
Open in new window