Insert records based on value

• bob_kudulis

  Say Hey Kid

  Points: 690

  More actions

  November 16, 2023 at 10:22 pm

  #4329127

  I have a query that returns how many weeks between 2 dates and would like to insert a record for every week.

  Equipment-Car

  Start Week-34

  Returned value=4

  Expected insert

  Equipment          Week

  Car                         35

  Car                         36

  Car                         37

  Car                         38

   

• Steve Collins

  SSCrazy

  Points: 2198

  More actions

  November 17, 2023 at 4:23 am

  #4329268

  From your query results you could CROSS APPLY a row generator created by using the row cardinality from sys.all_columns (which in my test instance is 11,745 rows) and TOP.  The (guessed) INSERT line is commented out

  with query_cte as (
      select 'car' as equipment, 34 as start_wk, 4 as returned_val
      union all
      select 'bike', 20, 3
      union all
      select 'truck', 10, 2)
  --insert into equipment_weeks(equipment, wk)
  select q.equipment, q.start_wk + t.n 
  from query_cte q
       cross apply (select top (q.returned_val) row_number() over (order by (select null)) as n
                    from sys.all_columns) t;
  
  select count(*)
  from sys.all_columns;
  /* current instance: 11745 */

   

  • This reply was modified 1 year, 6 months ago by  Steve Collins.

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können