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 |
|
abacusdotcom
Posting Yak Master
133 Posts |
 Posted - 2008-11-26 : 06:22:27
|
Hi all,I have some big tables (Table A,.. Table Y :D) i always get data that from everyday. I use the said data for a reporting services project.Using View to do this comes out very slow i think because of too many joins. So i create a separate table for this that i load into everyday ... I need help on incremental update so that i wont be calling truncate every time i load into the second table (Table Z)Sample query I use for load ...quote:
INSERT INTO [EDW].[dbo].[REP_OPERATIONS_DASHBOARD_ALL](KPI_ITEM,KPI_ITEM_GROUP,BRANCH,BRANCH_NAME,ZONE,REPORT_DATE ,PERIOD,COUNT_ALL ,CURRENCY_CODE,MEASURE,REPORT_FILE_NAME,FREE_TEXT_1,FREE_TEXT_2 ,FREE_TEXT_3 ,FREE_TEXT_4,FREE_TEXT_5,FREE_TEXT_6,FREE_TEXT_7,FREE_TEXT_8)SELECT 'ACCOUNTS REACTIVATED' AS KPI_ITEM, 'VOLUME STATISTICS - BRANCH OPERATIONS' AS KPI_ITEM_GROUP, DB.BRANCH_CODE AS BRANCH, DB.BRANCH_NAME AS BRANCH_NAME, DB.zonecodeops AS ZONE, CONVERT(VARCHAR(8),DT.AUDIT_DATE,112) AS REPORT_DATE, CONVERT(VARCHAR(6),DT.AUDIT_DATE,112) AS PERIOD, count(1) AS COUNT_ALL, DA.CURRENCY_CODE AS CURRENCY_CODE, NULL AS MEASURE, 'branch_kpi_report_details' AS REPORT_FILE_NAME, '' AS FREE_TEXT_1, '' AS FREE_TEXT_2, '' AS FREE_TEXT_3, '' AS FREE_TEXT_4, '' AS FREE_TEXT_5, '' AS FREE_TEXT_6, '' AS FREE_TEXT_7, '' AS FREE_TEXT_8from ODS.DBO.ADT DT,EDW.DBO.DIM_ACCOUNT DA,dwv_Branch DBWHERE DA.ACCOUNT_ID=DT.ACIDAND DA.BRANCH_CODE=DB.BRANCH_CODEand db.branch_type='REAL' and len(db.branch_code)=3AND DT.table_name IN ('CAM','SMT')AND DT.MODIFIED_FIELDS_DATA LIKE 'acct_status|D|A|%'AND DT.func_code='M'AND DT.AUDIT_SRL_NUM='001'group by db.branch_code, db.branch_name, dt.audit_date, da.currency_code,DB.zonecodeops
I want to build incremental update (update/insert) on this above query.ThanksI sign for fame not for shame but all the same, I sign my name. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-26 : 07:02:05
|
| Do you have any audit columns like datecreated,datemodified...on your source table? |
 |
|
|
abacusdotcom
Posting Yak Master
133 Posts |
Posted - 2008-11-26 : 07:09:40
|
quote:
Originally posted by visakh16
Do you have any audit columns like datecreated,datemodified...on your source table?
Visakh16You mean on tables A... Table Y, yes there is always a date key, transaction date etc...I sign for fame not for shame but all the same, I sign my name. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-26 : 07:25:47
|
then use a log table to held run date each time and use it to get incremental data asselect * from Table where transaction_date > (SELECT MAX(date) FROM LogTable) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-26 : 07:27:36
|
| it seems like what your table is used for warehousing (seeing key column). In such case, if you're using SSIS for population, then you've task for Slowing Chaging Dimension which captures incremental changes automatically. |
|
|
|
|
|
|
|
|
incremental updates