Dynamic Column update.

  • koti.raavi

    SSCrazy

    Points: 2694

    I would like to update TASK_NXT_RUN_DT  column based on the script in column 2 dynamically. I have tried using while loop and dynamic sql but no luck. Any help on this much appreciated

     

    create table #CFG_PROCESS_EVALUATE_TASK_MSTR (
    taskid int,
    TASK_SCHED_SQL_SCRIPT varchar(500),
    TASK_LST_RUN_DT datetime,
    TASK_NXT_RUN_DT datetime
    )
    insert into #CFG_PROCESS_EVALUATE_TASK_MSTR
    select 1,'dateadd(dd,1,TASK_NXT_RUN_DT)','2020-04-02 03:17:40.043', '2020-04-02 03:17:24.353' union all
    select 2,'dateadd(dd,10,TASK_NXT_RUN_DT)' ,'2020-04-02 03:17:40.043', '2020-04-02 03:17:24.353'

     

  • Phil Parkin

    SSC Guru

    Points: 244449

    koti.raavi wrote:

    I would like to update TASK_NXT_RUN_DT  column based on the script in column 2 dynamically. I have tried using while loop and dynamic sql but no luck. Any help on this much appreciated

    create table #CFG_PROCESS_EVALUATE_TASK_MSTR (
    taskid int,
    TASK_SCHED_SQL_SCRIPT varchar(500),
    TASK_LST_RUN_DT datetime,
    TASK_NXT_RUN_DT datetime
    )
    insert into #CFG_PROCESS_EVALUATE_TASK_MSTR
    select 1,'dateadd(dd,1,TASK_NXT_RUN_DT)','2020-04-02 03:17:40.043', '2020-04-02 03:17:24.353' union all
    select 2,'dateadd(dd,10,TASK_NXT_RUN_DT)' ,'2020-04-02 03:17:40.043', '2020-04-02 03:17:24.353'

    Have you considered using a different model? For example, create an INT column called 'DaysToAdd' (set it to 1, 10 in your example above). The UPDATE query then becomes trivial.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply