Incremental load - SQL Server Forums

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.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Incremental load

Author

Topic

jim123456789jim
Starting Member

13 Posts

Posted - 2013-12-22 : 12:07:51

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??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-22 : 13:23:25

1, You can use either MERGE statement to do all DML operations using single statement or use seperate INSERT,UPDATE,DELETE statements
2, You can use a derived table for that
something like


SELECT col1,Total,...
FROM (SELECT col1,SUM(col2) AS Total,..
      FROM table
      GROUP BY Col1)t
JOIN...

3, Same as 1 either using single MERGE or combination of INSERT/UPDATE/DELETE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-23 : 12:41:59

Is the destination table really just three columns or can you update the Income column based on the other two columns? If so, then you should be able to do the update as Visakh suggests. If not, then doing a delta on an aggregate table might be tough. However, if the table is really simple, then it may just be a matter of running the aggregate at the source and merging the results into the destination. Hard to say without more detail.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources