Insert new rows when item is added to another table SQL

All we need is an easy explanation of the problem, so here it is.

I have to tables. One dimension table with projects and one fact table with projects, value and a period(month).

Looking to add 12 new rows pr project ID into my fact table, when a new project is added to the dimension table. The 12 rows should be the next 12 months. (always the month following the current)

The issue I have is how to write a SQL statement that I can use with a trigger to make this work. I have tried some versions on INSERT INTO, but I’m not experienced enough to know have to write this statement.

example: project 2 has been added to dim table.

project status
1 A
2 B

Then create a trigger that adds these 12 new rows in my facttable

project status Period Value
2 B 6/1/2021 NULL
2 B 7/1/2021 NULL
2 B 8/1/2021 NULL
2 B 9/1/2021 NULL
2 B 10/1/2021 NULL
2 B 11/1/2021 NULL
2 B 12/1/2021 NULL
2 B 1/1/2022 NULL
2 B 2/1/2022 NULL
2 B 3/1/2022 NULL
2 B 4/1/2022 NULL
2 B 5/1/2022 NULL

Any suggestions on how to write this statement?

Thank you so much!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Create sp :

  1. declare variable for @project,@status
  2. insert into dimtable
    insert into dimtable
  1. Check if project is already exist in facttable.
  2. insert into #calender
    create table #calender(id int , date date)
    declare @count int=0
    insert into #calender
    SELECT @count+1,CONVERT(varchar,dateadd(day,1,dateadd(month,@count-1,EOMONTH(getdate()))), 101) as m_start
    set @count = @count + 1

    select * from #calender
  1. insert into facttable
    insert into facttable
    select @project, @status ,date, null from #calender

Method 2

If you want to avoid a trigger you can use the OUTPUT clause and a table variable:

drop table #pPeriod
drop table #dimProject
drop table #factProject

create table #dimProject (project int, status varchar(10))
declare @out table (project int, status varchar(10))
create table #factProject ( project int, status varchar(10), 
                            pPeriod datetime2, vValue varchar(10))
create table #pPeriod( pPeriod datetime2)

-- you will need more rows here
insert #pPeriod values ('20210501'),('20210601'),('20210701'),('20210801'),

insert into #dimProject (project, status)  
into @out
, status
values (1, 'A')

insert #factProject (project, status, pPeriod)
select o.project, o.status, p.pperiod
from @out o
cross join #pPeriod p
-- add a filter to join to todays date and fetch 12 months forward

select * from #dimProject

select * from #factProject

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply