Tuesday, 22 October 2013

Maintaining the data load history table manually after initial load

Until this feature is available in UI, here's how we can maintain the data load history table manually after the initial load.

1. Do the load of the entire file (or via DB Connect). Pick blank load period (or a specific value that you note down).

2. Note the mart id of the mart being loaded into.

3. Select * from nbmdc_dataload_history where nbmdc_nanomarts_row_id = <Your mart id> and tablename = <Your table name>
Note down the row_id of this. Use it below.

4. Change the row_id, mart table schema/name, mart table date column name in the statement below and run.

insert into nbmdc_dataload_history (
row_id    ,nbmds_users_row_id    ,nbmdc_nanomarts_row_id    ,nbmdc_nanomart_tables_row_id    ,icon_path    ,mis_date    ,loaded_date_of_data    ,updated_by_user_id,
    operation_mode    ,records_added    ,status_message    ,active_flag    ,tablename    ,martname    ,icon_content    ,created_by_user_id    ,records_deleted,
    records_updated    ,status_code    ,error_filepath    ,records_failed    ,time_taken    ,si_id    ,nbmdm_tenants_row_id    ,file_name
    )
select
t1.row_id  + '-' + cast((row_number() over (order by sysdatetime())) as varchar) row_id    ,
t1.nbmds_users_row_id    ,t1.nbmdc_nanomarts_row_id    ,t1.nbmdc_nanomart_tables_row_id    ,t1.icon_path    ,
t2.txn_date mis_date    ,
t1.loaded_date_of_data    ,
t1.updated_by_user_id,    t1.operation_mode    ,t1.records_added    ,t1.status_message    ,t1.active_flag    ,t1.tablename    ,t1.martname    ,t1.icon_content    ,
t1.created_by_user_id    ,t1.records_deleted    ,t1.records_updated    ,t1.status_code    ,t1.error_filepath    ,t1.records_failed    ,t1.time_taken    ,t1.si_id    ,t1.nbmdm_tenants_row_id    ,
t1.file_name
from nbmdc_dataload_history t1, qSFpsKqaycnaRKe.last_week_numbers t2
where t1.row_Id = '31334174-6ae2-4c55-bf4e-94b43bee401d';

-- delete the old entry as it is no longer required
delete from nbmdc_dataload_history
where row_Id = '31334174-6ae2-4c55-bf4e-94b43bee401d';

No comments:

Post a Comment