December 22, 2013 at 9:42 am
Hi,
We need to implement incremental load in database. A sample scenario is, there is a view (INCOMEVW) which is build on top of a query like
CREATE VIEW INCOMEVW
AS
SELECT CLIENTID,COUNTRYNAME,SUM(OUTPUT.INCOME) AS INCOME
(SELECT EOCLIENT_ID AS CLIENTID,EOCOUNTRYNAME AS COUNTRYNAME,EOINCOME AS INCOME FROM EOCLIENT C INNER JOIN EOCOUNTRY CT ON
C.COUNTRYCODE=CT.COUNTRYCODE
UNION ALL
SELECT ENCLIENT_ID AS CLIENTID,ENCOUNTRYNAME AS COUNTRYNAME,ENINCOMEAS as INCOME FROM ENCLIENT EC INNER JOIN ENCOUNTRY ECT ON
EC.COUNTRYCODE=ECT.COUNTRYCODE) OUTPUT
GROUP BY CLIENTID,COUNTRYNAME
This is a sample view. As of now there is a full load happening from the source(select * from INCOMEVW) and loads to target table tbl_Income.
We need to pick only the delta and load to the target table using a staging. The challenge is,
1) If we get the delta(Insert,update or deleted rows in the source tables EOCLIENT,EOCOUNTRY,ENCLIENT,ENCOUNTRY, how to load the incremental to
single target table tbl_Income.
2) How to do the Sum operation with group by in incremental load?
3) We are planning to have a daily incremental load and thinking to create the same table structure as source with Date and Flag column to identify
the date and whether that source row is an Insert or Update or Delete with the flag. But not sure how to frame something like this view and load to
single target with Sum operations.
Any suggestion??
January 10, 2014 at 8:28 am
Hi there,
Why can't you use a MERGE procedure? So load your delta data into your staging table and then use a merge stored procedure to add the delta value to the existing.
This should only happen for the Client IDs that have been changed.
Does that make sense?
Jim.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply