Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
 Posted - 2008-02-05 : 08:42:21
|
| Hi, I am facing a small problem while updating the warehouse using SSIS package. Here is an sample scenario. I have a staging table T1 with some important columns as stateid,ename etc .. and target table as Trg with only two columns stateid and statecnt. The Rule is, each state will be sending an excel file which basically contains state and employees belonging to the state information. For the first time when i execute the SSIS package, i need to load statetid and count_of_employees for that particular state. say for example. SELECT stateid, count(ename) from T1 where stateid = ? For the next time if the same state information is received then we need to update the count to the existing record in the Dataware house rather than creating a brand new record once again. I am little bit confused of implementig this logic. Can anyone help me out in accomplishing this task. What can done or what steps is involved in achiving the task. Thanks in advance. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-06 : 04:22:23
|
| You may use a SQL Command task to achieve it. the logic will be like:-UPDATE tSET t.statecnt=t1.statecntFROM Trg tINNER JOIN (SELECT stateid, count(ename) as statecnt from T1) t1ON t1.stateid= t.stateidINSERT into TrgSELECT stateid,count(ename) as statecntfrom T1 t1LEFT OUTER JOIN Trg tON t.stateid=t1.stateidWHERE t.stateid IS NULL |
 |
|
|
Qualis
Posting Yak Master
145 Posts |
Posted - 2008-02-07 : 13:31:50
|
| Don't forget your Group By stateid on your Selects. ;) |
|
|
|
|
|
|
Update logic in Warehouse ...