Answered by:
HOW TO.... Update Statement to backfill table with the "Result" of the SQL statement in field....

I have a table that has a mixture of data in it. It has normal table data, but also a field that has a literal SQL statement in it.
I am looking for an eaxy way, withough having to use a cursor to loop through each record one at a time, to simply update the table to have the RESULT of the execution of the SQL statement.
Here is an example.... This is not a real example of what I am doing, and the SQL is just to show you that there is a field in the table that has a literal SQL Stmt in it, rather than data.
NAME AGE PHONE SALARY

Bruce 58 5555555555 select (Exp_Years * Salary_Base) + (Bonus_Sched  Amt_Owed) from calcs
Steph 48 5555555555 select (Exp_Years * Salary_Base) + (Bonus_Sched  Amt_Owed) from calcs
Dalton 38 5555555554 select (Exp_Years * Salary_Base) + (Bonus_Sched  Amt_Owed) from calcs
Andrew 28 5555555553 select (Exp_Years * Salary_Base) + (Bonus_Sched  Amt_Owed) from calcs
Aubrie 28 5555555552 select (Exp_Years * Salary_Base) + (Bonus_Sched  Amt_Owed) from calcs
Madison 28 5555555551 select (Exp_Years * Salary_Base) + (Bonus_Sched  Amt_Owed) from calcs
QUESTION  So, using an UPDATE STATEMENT (or something else other than a cursor) how would I get this....
NAME AGE PHONE SALARY

Bruce 58 5555555555 30000.00
Steph 48 5555555555 40000.00
Dalton 38 5555555554 50000.00
Andrew 28 5555555553 60000.00
Aubrie 28 5555555552 70000.00
Madison 28 5555555551 80000.00
FYI  I am not opposed to cursors, however, I have tried it in my application (for this specific issue) and it takes about 5 minutes to run due to the number of recrds that I am processing... I know some people refuse to use cursors, that is not me, I use them a lot but it just will not work here. I have also tried a #TEMP table with a clustered index and going item by item. That was the same as using the cursor...
Question
Answers

Problem solved.. Here was the solution…. After I create the #Temp table, I do the following code… Works like a charm… A guy by the name of Mikhail Bisserov helped me out with this … Here is a link to the solution..
SELECT @SQLString = 'UPDATE #TEMP_WORK '
+ ' SET [Field_7_MonthtoDate] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]','EXMTD',' ',' ') + ')'
+ ' ,[Field_8_YeartoDate] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]', 'EXCYTD',' ',' ') + ')'
+ ' ,[Field_9_FiscaltoDate] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]', 'EXFTD',' ',' ') + ')'
+ ' ,[Field_10_Balance] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]', 'CBUNEN',' ',' ') + ')'
+ ' ,[Field_11_Pending] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]', 'EXP',' ',' ') + ')'
+ ' ,[Field_12_Pending] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]', 'PBUNEN',' ',' ') + ')'
EXEC(@SQLString)
Before, I was testing with 1600 rows of data, with 6 calculations per row (9600 calculations total), and it took 5+ minutes.
Now, I just ran it with 2791 rows of data, with 6 calculations per row (16,476 calculations total), and it took 45 sec. (Much better)
Thanks for your help with this… Bruce
 Marked as answer by Jinchun ChenMicrosoft employee, Moderator Monday, May 04, 2009 3:25 AM
All replies

UPDATE your_table
SET salary = Convert(varchar(25), (c.exp_years * c.salary_base) + (c.bonus_sched  c.amt_owned))
FROM your_table As x
INNER JOIN calcs As c
ON x.primary_key_field = c.foreign_key_field
WHERE x.salary = 'select (Exp_Years * Salary_Base) + (Bonus_Sched  Amt_Owed) from calcs'
George 

Hey Bruce,
The query written by George above should suffice your needs. However, I think the "Where" clause in George's query can be omitted, the query will work fine without the "Where" clause as well.
And I have a doubt here. Is there any particular reason for storing the query itself in the "Salary" column ? One thing that I can think of for this is the Salary calculation might differ for different employees. However, in your example, all the queries are same. I do not see any reason to save the query itself in the "Salary" column if the calculation formula is the same for all employees. And George's query should work fine for you.
I am assuming here that the Calcs table will have only 1 record for each employee.
Thanks,
Shalin
Kapadia Shalin P. 
Ok... I so much appreciate your time... Let me show you what it is I am trying to do and see if this helps... What I am doing has nothing to do with salary calculations, I just did that as a generic example, one that would be easier to explain than this....
inside of a Stored Procedure, here is the literal SQL that I am creating to execute.
SELECT @SQL_PART_1 = 'select '
+ ' ERACCTiID as ''RECID'''
+ ' ,(select FUNDsFundNumber from acctFunds_T T2 where T2.FUNDiID = T1.ERACCTiFundID) as ''Field_1_Fund'' '
+ ' ,(select ACCTPORsNumber from acctAccountPortions_T T2 where T2.ACCTPORiID = T1.ERACCTiPortion2) as ''Field_2_Account'' '
+ ' ,(select ACCTPORsNumber from acctAccountPortions_T T2 where T2.ACCTPORiID = T1.ERACCTiPortion3) as ''Field_3_Object'' '
+ ' ,(select ACCTPORsNumber from acctAccountPortions_T T2 where T2.ACCTPORiID = T1.ERACCTiPortion4) as ''Field_4_Cost Ctr'' '
+ ' ,(select ACCTPORsNumber from acctAccountPortions_T T2 where T2.ACCTPORiID = T1.ERACCTiPortion5) as ''Field_5_Location'' '
+ ' ,T1.ERACCTsAccountTitle as ''Field_6_Account Title'' '
+ ' , dbo.Expenditure_F(ERACCTiID,''EXMTD'','' '','' '') as ''Field_7_MonthtoDate'' '
+ ' , dbo.Expenditure_F(ERACCTiID,''EXCYTD'','' '','' '') as ''Field_8_YeartoDate'' '
+ ' , dbo.Expenditure_F(ERACCTiID,''EXFTD'','' '','' '') as ''Field_9_FiscaltoDate'' '
+ ' , dbo.Expenditure_F(ERACCTiID,''CBUNEX'','' '','' '') as ''Field_10_Balance'' '
+ ' , dbo.Expenditure_F(ERACCTiID,''EXP'','' '','' '') as ''Field_11_Pending'' '
+ ' , 9999.99 as ''Field_12_Pending'' '
+ ' from acctExpRevAccounts_T T1 '
The functions calls I have highighed preform some rather complex calculations and return the literal SQL that is used to generate the calculations.. So, the following wold be a sample of the SQL that is returned from each.
Here is what is returned from the above function call.... (One of these would be returned for each call above.)
SELECT ISNULL(SUM(tmultidblamount),0)*1 as TheValue from acctTransactionMain_T TM inner join acctTransactionDetail_T TD on TD.TDETAIL_TMAINiID = TM.TMAINiID inner join acctTransactionMulti_T TML on TML.TMULTI_TDETAILiID = TD.TDETAILiID inner join acctExpRevAccounts_T EX on TML.TMULTI_ERACCTiID = EX.ERACCTiID WHERE ERACCTiID = 103990 AND datepart(month,TDETAILdtLineDate) = datepart(month,getdate()) AND datepart(year,TDETAILdtLineDate) = datepart(year,getdate()) AND convert(datetime, TDETAILdtLineDate) <= convert(datetime, 'Apr 18 2009 11:19PM') AND TMAINiTransactionStatus <> 300 AND ERACCTsType = 'E' AND TMAINsType in ( 'V', 'ET', 'FT' )
So the above SQL would be inside of the field for eadh recod in place of the sql in my example of the salary calculation...
I am so appreciative of your help and look forward to your reply..
Thanks,
Bruce

Ok... I so much appreciate your time... Let me show you what it is I am trying to do and see if this helps... What I am doing has nothing to do with salary calculations, I just did that as a generic example, one that would be easier to explain than this....
inside of a Stored Procedure, here is the literal SQL that I am creating to execute.
SELECT @SQL_PART_1 = 'select '
+ ' ERACCTiID as ''RECID'''
+ ' ,(select FUNDsFundNumber from acctFunds_T T2 where T2.FUNDiID = T1.ERACCTiFundID) as ''Field_1_Fund'' '
+ ' ,(select ACCTPORsNumber from acctAccountPortions_T T2 where T2.ACCTPORiID = T1.ERACCTiPortion2) as ''Field_2_Account'' '
+ ' ,(select ACCTPORsNumber from acctAccountPortions_T T2 where T2.ACCTPORiID = T1.ERACCTiPortion3) as ''Field_3_Object'' '
+ ' ,(select ACCTPORsNumber from acctAccountPortions_T T2 where T2.ACCTPORiID = T1.ERACCTiPortion4) as ''Field_4_Cost Ctr'' '
+ ' ,(select ACCTPORsNumber from acctAccountPortions_T T2 where T2.ACCTPORiID = T1.ERACCTiPortion5) as ''Field_5_Location'' '
+ ' ,T1.ERACCTsAccountTitle as ''Field_6_Account Title'' '
+ ' , dbo.Expenditure_F(ERACCTiID,''EXMTD'','' '','' '') as ''Field_7_MonthtoDate'' '
+ ' , dbo.Expenditure_F(ERACCTiID,''EXCYTD'','' '','' '') as ''Field_8_YeartoDate'' '
+ ' , dbo.Expenditure_F(ERACCTiID,''EXFTD'','' '','' '') as ''Field_9_FiscaltoDate'' '
+ ' , dbo.Expenditure_F(ERACCTiID,''CBUNEX'','' '','' '') as ''Field_10_Balance'' '
+ ' , dbo.Expenditure_F(ERACCTiID,''EXP'','' '','' '') as ''Field_11_Pending'' '
+ ' , 9999.99 as ''Field_12_Pending'' '
+ ' from acctExpRevAccounts_T T1 '
The functions calls I have highighed preform some rather complex calculations and return the literal SQL that is used to generate the calculations.. So, the following wold be a sample of the SQL that is returned from each.
Here is what is returned from the above function call.... (One of these would be returned for each call above.)
SELECT ISNULL(SUM(tmultidblamount),0)*1 as TheValue from acctTransactionMain_T TM inner join acctTransactionDetail_T TD on TD.TDETAIL_TMAINiID = TM.TMAINiID inner join acctTransactionMulti_T TML on TML.TMULTI_TDETAILiID = TD.TDETAILiID inner join acctExpRevAccounts_T EX on TML.TMULTI_ERACCTiID = EX.ERACCTiID WHERE ERACCTiID = 103990 AND datepart(month,TDETAILdtLineDate) = datepart(month,getdate()) AND datepart(year,TDETAILdtLineDate) = datepart(year,getdate()) AND convert(datetime, TDETAILdtLineDate) <= convert(datetime, 'Apr 18 2009 11:19PM') AND TMAINiTransactionStatus <> 300 AND ERACCTsType = 'E' AND TMAINsType in ( 'V', 'ET', 'FT' )
So the above SQL would be inside of the field for eadh recod in place of the sql in my example of the salary calculation...
I am so appreciative of your help and look forward to your reply..
Thanks,
Bruce

Your database design is severely flawed in that case.
Your only solution is going to be using dynamic SQL (argh!) and cursors (eww!).
If you can provide the output columns (i.e. sample data) from your SQL then that will give us something better to start from.
George 
I am not sure how you are coming to the conclusion that my database design is flawed. The reason I am having to try and execute the SQL this way, has nothing to do with the design of my database. It has to do with the fact that SQL Server will not allow any execution of SQL inside of functions. The calculation functions I use prepares the SQL just as it should, but I can not execute it and return the result from the function. All I can do is return the SQL from the function. That, combined with the fact, I can not find anyone to tell me how to execute the SQL with a single update statement does not make my database design flawed, it just makes this a dificult issue. I will find a solution to this and I appreciate your offer to help...

Problem solved.. Here was the solution…. After I create the #Temp table, I do the following code… Works like a charm… A guy by the name of Mikhail Bisserov helped me out with this … Here is a link to the solution..
SELECT @SQLString = 'UPDATE #TEMP_WORK '
+ ' SET [Field_7_MonthtoDate] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]','EXMTD',' ',' ') + ')'
+ ' ,[Field_8_YeartoDate] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]', 'EXCYTD',' ',' ') + ')'
+ ' ,[Field_9_FiscaltoDate] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]', 'EXFTD',' ',' ') + ')'
+ ' ,[Field_10_Balance] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]', 'CBUNEN',' ',' ') + ')'
+ ' ,[Field_11_Pending] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]', 'EXP',' ',' ') + ')'
+ ' ,[Field_12_Pending] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]', 'PBUNEN',' ',' ') + ')'
EXEC(@SQLString)
Before, I was testing with 1600 rows of data, with 6 calculations per row (9600 calculations total), and it took 5+ minutes.
Now, I just ran it with 2791 rows of data, with 6 calculations per row (16,476 calculations total), and it took 45 sec. (Much better)
Thanks for your help with this… Bruce
 Marked as answer by Jinchun ChenMicrosoft employee, Moderator Monday, May 04, 2009 3:25 AM

Problem solved.. Here was the solution…. After I create the #Temp table, I do the following code… Works like a charm… A guy by the name of Mikhail Bisserov helped me out with this … Here is a link to the solution..
SELECT @SQLString = 'UPDATE #TEMP_WORK '
+ ' SET [Field_7_MonthtoDate] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]','EXMTD',' ',' ') + ')'
+ ' ,[Field_8_YeartoDate] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]', 'EXCYTD',' ',' ') + ')'
+ ' ,[Field_9_FiscaltoDate] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]', 'EXFTD',' ',' ') + ')'
+ ' ,[Field_10_Balance] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]', 'CBUNEN',' ',' ') + ')'
+ ' ,[Field_11_Pending] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]', 'EXP',' ',' ') + ')'
+ ' ,[Field_12_Pending] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]', 'PBUNEN',' ',' ') + ')'
EXEC(@SQLString)
Before, I was testing with 1600 rows of data, with 6 calculations per row (9600 calculations total), and it took 5+ minutes.
Now, I just ran it with 2791 rows of data, with 6 calculations per row (16,476 calculations total), and it took 45 sec. (Much better)
Thanks for your help with this… Bruce