Insert Dummy Rows To Fill Missing Values Into A Sql Table

I have this SQL Server table table1 which I want to fill with dummy rows per acct up to latest previous month end date period e.g now would be up to 2021-06-30. In this example, ac

Solution 1:

You can generate the rows using a recursive CTE:

with cte as (
      select acct, amt,
             dateadd(day, 1, end_date) as begin_date,
             eomonth(dateadd(day, 1, end_date)) as end_date
      from (select t.*,
                   row_number() over (partitionby acct orderby end_date desc) as seqnum
            from t
           ) t
      where seqnum =1and end_date <'2021-06-30'unionallselect acct, amt, dateadd(month, 1, begin_date),
             eomonth(dateadd(month, 1, begin_date))
      from cte
      where begin_date <'2021-06-01'
     )
select*from cte;

You can then use insert to insert these rows into a table. Or use union all if you simply want a result set with all the rows.

Here is a db<>fiddle.