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

    Question

  • 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        555-555-5555    select (Exp_Years * Salary_Base) + (Bonus_Sched - Amt_Owed) from calcs
    Steph        48        555-555-5555    select (Exp_Years * Salary_Base) + (Bonus_Sched - Amt_Owed) from calcs
    Dalton       38        555-555-5554    select (Exp_Years * Salary_Base) + (Bonus_Sched - Amt_Owed) from calcs
    Andrew      28        555-555-5553    select (Exp_Years * Salary_Base) + (Bonus_Sched - Amt_Owed) from calcs
    Aubrie       28        555-555-5552    select (Exp_Years * Salary_Base) + (Bonus_Sched - Amt_Owed) from calcs
    Madison     28        555-555-5551    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        555-555-5555    30000.00
    Steph        48        555-555-5555    40000.00
    Dalton       38        555-555-5554    50000.00
    Andrew      28        555-555-5553    60000.00
    Aubrie       28        555-555-5552    70000.00
    Madison     28        555-555-5551   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...
    Sunday, April 19, 2009 12:36 PM

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..

     

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/decc6602-323f-4c45-abcb-4f7bf30021bc

     

    SELECT @SQLString = 'UPDATE #TEMP_WORK '

    + ' SET [Field_7_Month-to-Date] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]','EXMTD',' ',' ') + ')'

    + ' ,[Field_8_Year-to-Date] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]', 'EXCYTD',' ',' ') + ')'

    + ' ,[Field_9_Fiscal-to-Date] = (' + 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

    Thursday, April 23, 2009 7:31 PM

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
    Sunday, April 19, 2009 2:31 PM
    Answerer
  • Pah, formatting got ruined when editing...
    George
    Sunday, April 19, 2009 2:35 PM
    Answerer
  • 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.
    Sunday, April 19, 2009 3:23 PM
  • 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_Month-to-Date'' '
    + ' , dbo.Expenditure_F(ERACCTiID,''EXCYTD'','' '','' '') as ''Field_8_Year-to-Date'' '
    + ' , dbo.Expenditure_F(ERACCTiID,''EXFTD'','' '','' '') as ''Field_9_Fiscal-to-Date'' '
    + ' , 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

     

     

    P.S. If you would rather e-mail me I can send you some specific examples...  pullum4545@yahoo.com
    Sunday, April 19, 2009 6:29 PM
  • 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_Month-to-Date'' '
    + ' , dbo.Expenditure_F(ERACCTiID,''EXCYTD'','' '','' '') as ''Field_8_Year-to-Date'' '
    + ' , dbo.Expenditure_F(ERACCTiID,''EXFTD'','' '','' '') as ''Field_9_Fiscal-to-Date'' '
    + ' , 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

     

     

    P.S. If you would rather e-mail me I can send you some specific examples...  pullum4545@yahoo.com
    Sunday, April 19, 2009 6:30 PM
  • 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
    Wednesday, April 22, 2009 4:25 PM
    Answerer
  • 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...
    Thursday, April 23, 2009 10:39 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..

     

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/decc6602-323f-4c45-abcb-4f7bf30021bc

     

    SELECT @SQLString = 'UPDATE #TEMP_WORK '

    + ' SET [Field_7_Month-to-Date] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]','EXMTD',' ',' ') + ')'

    + ' ,[Field_8_Year-to-Date] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]', 'EXCYTD',' ',' ') + ')'

    + ' ,[Field_9_Fiscal-to-Date] = (' + 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

    Thursday, April 23, 2009 7:31 PM
  • 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..

     

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/decc6602-323f-4c45-abcb-4f7bf30021bc

     

    SELECT @SQLString = 'UPDATE #TEMP_WORK '

    + ' SET [Field_7_Month-to-Date] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]','EXMTD',' ',' ') + ')'

    + ' ,[Field_8_Year-to-Date] = (' + dbo.Expenditure_F('#TEMP_WORK.[RECID]', 'EXCYTD',' ',' ') + ')'

    + ' ,[Field_9_Fiscal-to-Date] = (' + 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

    Thursday, April 23, 2009 7:35 PM