Dynamic Column update.

• koti.raavi

  SSCrazy

  Points: 2694

  More actions

  April 2, 2020 at 11:30 am

  #3739941

  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

  More actions

  April 2, 2020 at 1:58 pm

  #3739994

  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.