Insert new rows when item is added to another table SQL 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!
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