Insert new rows when item is added to another table SQL
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'),
                       ('20210901'),('20211001'),('20211101'),('20211201')

insert into #dimProject (project, status)  
output 
  inserted.project, 
  inserted.status 
into @out
(
  project
, 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