sql server – 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
sorry about the formatting – this web page has a mind of its own

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