How do I insert into a multi keyed table with a sequence no

I want to insert a new Task into the following table for each unique ID_COST_CENTRE, NM_ROSTER and set the value of No_Order_Number to the MAX(No_Order_Number)+1.

I can set the NM_TASK, TY_TASK & ID_TASK_TYPE values

INSERT INTO [Microster].[dbo].[MIC_CC_Task_Edit]
   ([ID_COST_CENTRE],[NM_ROSTER],[NM_TASK],[No_Order_Number],[TY_TASK],[ID_TASK_TYPE])
   VALUES
   (<ID_COST_CENTRE, char(15),>
   ,<NM_ROSTER, char(25),>
   ,<NM_TASK, char(15),>
   ,<No_Order_Number, smallint,>
   ,<TY_TASK, smallint,>
   ,<ID_TASK_TYPE, char(15),>)

For example I want to add the task AL to the following ang get the result below.

0006    0006 ADMIN              CLERICAL ASSIST 1   1   Worked Task 
000开发者_JAVA百科6    0006 ADMIN          COORD       2   1   Worked Task 
0006    0006 ADMIN          MRT             3   1   Worked Task 
0006    0006 ADMIN          ORIENT      4   1   Worked Task 
0006    0006 MRT'S          CLERICAL ASSIST 1   1   Worked Task 
0006    0006 MRT'S          COORD       2   1   Worked Task 
0006    0006 MRT'S          MRT             3   1   Worked Task 
0006    0006 SONOGRAPHER'S      CLERICAL ASSIST 1   1   Worked Task 
0006    0006 SONOGRAPHER'S      COORD       2   1   Worked Task 
0006    0006 SONOGRAPHER'S      MRT             3   1   Worked Task 
0006    0006 SONOGRAPHER'S      ORIENT      4   1   Worked Task 
0006    0006 SONOGRAPHER'S      SONOGRAPHER     5   1   Worked Task

after insert

0006    0006 ADMIN          CLERICAL ASSIST 1   1   Worked Task 
0006    0006 ADMIN          COORD       2   1   Worked Task 
0006    0006 ADMIN          MRT             3   1   Worked Task 
0006    0006 ADMIN          ORIENT      4   1   Worked Task 
0006    0006 ADMIN          AL          5   3   AL 
0006    0006 MRT'S          CLERICAL ASSIST 1   1   Worked Task 
0006    0006 MRT'S          COORD       2   1   Worked Task 
0006    0006 MRT'S          MRT             3   1   Worked Task 
0006    0006 MRT'S          AL          4   3   AL 
0006    0006 SONOGRAPHER'S      CLERICAL ASSIST 1   1   Worked Task 
0006    0006 SONOGRAPHER'S      COORD       2   1   Worked Task 
0006    0006 SONOGRAPHER'S      MRT             3   1   Worked Task 
0006    0006 SONOGRAPHER'S      ORIENT      4   1   Worked Task 
0006    0006 SONOGRAPHER'S      SONOGRAPHER     5   1   Worked Task 
0006    0006 SONOGRAPHER'S      AL      6   3   AL 

Try it first without the INSERT INTO:

INSERT INTO [Microster].[dbo].[MIC_CC_Task_Edit]
   ( [ID_COST_CENTRE]
   , [NM_ROSTER]
   , [NM_TASK]
   , [No_Order_Number]
   , [TY_TASK]
   , [ID_TASK_TYPE]
   )
  SELECT 
       ID_COST_CENTRE
     , NM_ROSTER
     , 'AL'
     , MAX(No_Order_Number) + 1
     , 3
     , 'AL'
  FROM
      [Microster].[dbo].[MIC_CC_Task_Edit]
  GROUP BY
       ID_COST_CENTRE
     , NM_ROSTER