-1

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!

1

2 Answers 2

0

Create sp :

  1. declare variable for @project,@status
  2. insert into dimtable
    insert into dimtable
    values(@project,@status)
  1. Check if project is already exist in facttable.
  2. 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
  1. insert into facttable
    insert into facttable
    select @project, @status ,date, null from #calender
0

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
1
  • 1
    Why would you want to avoid triggers? It's precisely what they're for, imho. May 4, 2021 at 14:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.