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.
Then create a trigger that adds these 12 new rows in my facttable
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.
Create sp :
- declare variable for @project,@status
- insert into dimtable
insert into dimtable values(@project,@status)
- Check if project is already exist in facttable.
- insert into #calender
create table #calender(id int , date date) declare @count int=0 while(@count<12) begin 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 end select * from #calender
- insert into facttable
insert into facttable select @project, @status ,date, null from #calender
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
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂