MaryJaneDoane
Programmer
Hi I am creting a DTS package to update, append and delete records from one table to another. Everything is worknig except the append. I removed 10 records from the table I am updating. My append statement isn't inserting the 10 records. The table it copies into is a history table that has records for each month. My append statement is below.
insert into history (history.note_id,history.as_of,history.account, history.note,history.system, history.officer,history.status,history.grade,history.branch, history.total_code, history.purch_pool, history.orig_amount, history.orig_purch_bal, history.current_balance, history.accrued_in, history.note_entry, history.orig_date, history.last_tran, history.nxt_p_due, history.nxt_i_due,history.next_review,history.nastatus,history.orig_mat, history.actual_mat,history.est_mat,history.closed,history.interest_rate, history.eff_rate, history.rate_margin,history.prime_ind, history.prime_nx_ind, history.prime_tier,history.delay_ind, history.delay, history.delay_incr, history.basis_code, history.ceiling, history.floor, history.pmt_code, history.bill_method, history.monthly_pa, history.prin_freq,history.int_freq,history.prin_due, history.int_due, history.pmt_amt, history.pd_actual, history.pd_report, history.prior_pd_d, history.pd_30, history.pd_60,history.pd_90,history.prin_to_date,history.pmtdiff, history.adj_pmts,history.settle_pmts,history.amt_tfr,history.ad, history.ad_accretion,history.ad_acc_to_date, history.ad_xfer, history.nad, history.nad_accretion,history.nad_acc_to_date, history.nad_xfer, history.dd_nad, history.acc_status,history.reg_co, history.nad_co, history.spec_reserve, history.ttl_pmt_rec, history.coll_fut_int, history.uncoll_fut_int, history.ttl_ad, history.ttl_nad, history.ttl_fut_int, history.air_reserve, history.air_res_amt,history.oreo_bal, history.co_indicator,history.error_amt,history.err2,history.review_inc,history.lc_code, history.loan_type,history.special_note,history.collateral, history.property, history.total_coll_val, history.lendable_coll_val, history.regulatory, history.purpose,history.sic, history.shortname, history.ppna,history.iana,history.pana,history.parti_amt,history.sba_indicator,history.a_nxrtch, history.a_origdue, history.a_rtchfreq,history.a_comm, history.a_effdate,history.a_caps,history.a_margin,history.a_index,history.a_amort, history.a_pmt_code,history.restructure_ind, history.oacct_num, history.onote_num, history.serviced, history.admin_demand,history.kmc_due,history.serv_acct,history.kmc_eff_date,history.cap_costs, history.esc_amt, history.esc_bal, history.rest_chgoff, history.y2000,history.term, history.variance_da, history.p_i_pmt, history.aml_code,history.nxt_aml_an_da,history.plan_no, history.edit_date,history.sec_mtg_code,history.action_pla,history.recon_other_amt,history.recon_tc,history.act_restru,history.abt_status,history.non_recourse,history.extend_for_sale,history.seco_mtg,history.late_charges,history.high_ltv,history.high_env_risk,history.y2k_sent,history.amt_due,history.rate_code,history.hmda,history.fed,history.accreted,history.note_liab_code,history.note_loan_stat,history.recon_loan_stat,history.note_paid_ind,history.variable_plan,history.bill_type,history.past_due_amt,history.nxt_pmt_due_dte,history.loan_cat,history.pd_120,history.pd_15,history.gen_ledg_bal,history.chgoff,history.cocatnum,history.pi_freq,history.lnnuse1,history.nxt_pmt_chg_dte,history.acctclosedate,history.pmttermchg,history.forsale,history.pmt_freq,history.expt_mat,history.sale_pool,history.cmmt_nbr,history.int_purch,history.int_res,history.prior_15,history.prior_30,history.prior_60,history.prior_90,history.prior_120,history.non_acc_at_purch,history.fdic_classified,history.classified_date,history.loan_assumption,history.rltd_bnk_part_nbr,history.tdr,history.bnk_shr_accretion,history.assumed_ind,history.assumption_date,
history.GLAverage,history.cltv_glb,history.cltv_ni,history.ltv_glb,history.ltv_ni,history.ltv_autocomm,history.ltv_mancomm,history.ltv_mannibcltv,history.ltv_manglltv,history.ltv_manover,history.ltv_upbltv,history.ltv_upbcltv,history.ltv_manupbltv,history.frbnotl,history.watchlist,history.payoff,history.sbaloanproggrp,history.use_of_proceeds,history.opandi,history.origrate,history.ioterm,history.ltv_org_a,history.cltv_org_a,history.ltv_org_m,history.cltv_org_m,history.fradate,history.fpadate,history.securnt,history.ioenddt,history.note_mod_date,history.first_pay_date,history.num_collat,history.lastpymtfromsell,history.cnfrmlstpymtfromsell,history.secrdyloanops,history.secrdyassetmgr,history.fpercap,history.mpercap,history.aloanid,history.mtsid)
select tempHist.note_id, tempHist.as_of, tempHist.bisys_account,tempHist.bisys_note,tempHist.system, tempHist.officer,tempHist.status,tempHist.grade,tempHist.branch, tempHist.total_code, tempHist.purch_pool, tempHist.orig_amount, tempHist.orig_purch_bal, tempHist.current_balance, tempHist.accrued_in, tempHist.note_entry, tempHist.orig_date, tempHist.last_tran, tempHist.nxt_p_due, tempHist.nxt_i_due,tempHist.next_review,tempHist.nastatus,tempHist.orig_mat, tempHist.actual_mat,tempHist.est_mat,tempHist.closed,tempHist.interest_rate, tempHist.eff_rate, tempHist.rate_margin,tempHist.prime_ind, tempHist.prime_nx_ind, tempHist.prime_tier,tempHist.delay_ind, tempHist.delay, tempHist.delay_incr, tempHist.basis_code, tempHist.ceiling, tempHist.floor, tempHist.pmt_code, tempHist.bill_method, tempHist.monthly_pa, tempHist.prin_freq,tempHist.int_freq,tempHist.prin_due, tempHist.int_due, tempHist.pmt_amt, tempHist.pd_actual, tempHist.pd_report, tempHist.prior_pd_d, tempHist.pd_30, tempHist.pd_60,tempHist.pd_90,tempHist.prin_to_date,tempHist.pmtdiff, tempHist.adj_pmts,tempHist.settle_pmt,tempHist.amt_tfr,tempHist.ad, tempHist.ad_accretion,tempHist.ad_acc_to_date, tempHist.ad_xfer, tempHist.nad, tempHist.nad_accretion,tempHist.nad_acc_to_date, tempHist.nad_xfer, tempHist.dd_nad, tempHist.acc_status,tempHist.reg_co, tempHist.nad_co, tempHist.spec_reserve, tempHist.ttl_pmt_rec, tempHist.coll_fut_int, tempHist.uncoll_fut_int, tempHist.ttl_ad, tempHist.ttl_nad, tempHist.ttl_fut_int, tempHist.air_reserve, tempHist.air_res_amt,tempHist.oreo_bal, tempHist.bisys_co_indicator,tempHist.bisys_error_amt,tempHist.bisys_err2,tempHist.review_inc,tempHist.lc_code, tempHist.loan_type,tempHist.special_note,tempHist.collateral, tempHist.property, tempHist.total_coll_val, tempHist.lendable_coll_val, tempHist.regulatory, tempHist.purpose,tempHist.sic, tempHist.shortname, tempHist.ppna,tempHist.iana,tempHist.pana,tempHist.parti_amt,tempHist.sba_indicator,tempHist.a_nxrtch, tempHist.a_origdue, tempHist.a_rtchfreq,tempHist.a_comm, tempHist.a_effdate,tempHist.a_caps,tempHist.a_margin,tempHist.a_index,tempHist.a_amort, tempHist.a_pmt_code,tempHist.restructure_ind, tempHist.oacct_num, tempHist.onote_num, tempHist.serviced, tempHist.admin_demand,tempHist.kmc_due,tempHist.serv_acct,tempHist.kmc_eff_date,tempHist.cap_costs, tempHist.esc_amt, tempHist.esc_bal, tempHist.rest_chgoff, tempHist.y2000,tempHist.term, tempHist.variance_date, tempHist.p_i_pmt, tempHist.aml_code,tempHist.nxt_aml_an_da,tempHist.plan_no, tempHist.edit_date,tempHist.sec_mtg_code,tempHist.action_pla,tempHist.recon_other_amt,tempHist.recon_tc,tempHist.act_restru,tempHist.abt_status,tempHist.non_recourse,tempHist.extend_for_sale,tempHist.seco_mtg,tempHist.late_charges,tempHist.high_ltv,tempHist.high_env_risk,tempHist.y2k_sent,tempHist.amt_due,tempHist.rate_code,tempHist.hmda,tempHist.fed,tempHist.accreted,tempHist.note_liab_code,tempHist.note_loan_stat,tempHist.recon_loan_stat,tempHist.note_paid_ind,tempHist.variable_plan,tempHist.bill_type,tempHist.past_due_amt,tempHist.nxt_pmt_due_dte,tempHist.loan_cat,tempHist.pd_120,tempHist.pd_15,tempHist.gen_ledg_bal,tempHist.chgoff,tempHist.cocatnum,tempHist.pi_freq,tempHist.lnnuse1,tempHist.nxt_pmt_chg_dte,tempHist.acctclosedate,tempHist.pmttermchg,tempHist.forsale,tempHist.pmt_freq,tempHist.expt_mat,tempHist.sale_pool,tempHist.cmmt_nbr,tempHist.int_purch,tempHist.int_res,tempHist.prior_15,tempHist.prior_30,tempHist.prior_60,tempHist.prior_90,tempHist.prior_120,tempHist.non_acc_at_purch,tempHist.fdic_classified,tempHist.classified_date,tempHist.loan_assumption,tempHist.rltd_bnk_part_nbr,tempHist.tdr,tempHist.bank_shr_accretion,tempHist.assumed_ind,tempHist.assumption_date,tempHist.GLAverage,tempHist.cltv_glb,tempHist.cltv_ni,tempHist.ltv_glb,tempHist.ltv_ni,tempHist.ltv_autocomm,tempHist.ltv_mancomm,tempHist.ltv_mannibcltv,tempHist.ltv_manglltv,tempHist.ltv_manover,tempHist.ltv_upbltv,tempHist.ltv_upbcltv,tempHist.ltv_manupbltv,tempHist.frbnotl,tempHist.watchlist,tempHist.payoff,tempHist.sbaloanproggrp,tempHist.use_of_proceeds,tempHist.opandi,tempHist.origrate,tempHist.ioterm,tempHist.ltv_org_a,tempHist.cltv_org_a,tempHist.ltv_org_m,tempHist.cltv_org_m,tempHist.fradate,tempHist.fpadate,tempHist.securnt,tempHist.ioenddt,tempHist.note_mod_date,tempHist.first_pay_date,tempHist.num_collat,tempHist.lastpymtfromsell,tempHist.cnfrmlstpymtfromsell,tempHist.secrdyloanops,tempHist.secrdyassetmgr,tempHist.fpercap,tempHist.mpercap,tempHist.aloanid,tempHist.mtsid
FROM temphist LEFT JOIN history ON temphist.note_id = history.note_id
WHERE (history.as_of is null AND HISTORY.NOTE_ID IS NULL)
Any ideas?
Mary
insert into history (history.note_id,history.as_of,history.account, history.note,history.system, history.officer,history.status,history.grade,history.branch, history.total_code, history.purch_pool, history.orig_amount, history.orig_purch_bal, history.current_balance, history.accrued_in, history.note_entry, history.orig_date, history.last_tran, history.nxt_p_due, history.nxt_i_due,history.next_review,history.nastatus,history.orig_mat, history.actual_mat,history.est_mat,history.closed,history.interest_rate, history.eff_rate, history.rate_margin,history.prime_ind, history.prime_nx_ind, history.prime_tier,history.delay_ind, history.delay, history.delay_incr, history.basis_code, history.ceiling, history.floor, history.pmt_code, history.bill_method, history.monthly_pa, history.prin_freq,history.int_freq,history.prin_due, history.int_due, history.pmt_amt, history.pd_actual, history.pd_report, history.prior_pd_d, history.pd_30, history.pd_60,history.pd_90,history.prin_to_date,history.pmtdiff, history.adj_pmts,history.settle_pmts,history.amt_tfr,history.ad, history.ad_accretion,history.ad_acc_to_date, history.ad_xfer, history.nad, history.nad_accretion,history.nad_acc_to_date, history.nad_xfer, history.dd_nad, history.acc_status,history.reg_co, history.nad_co, history.spec_reserve, history.ttl_pmt_rec, history.coll_fut_int, history.uncoll_fut_int, history.ttl_ad, history.ttl_nad, history.ttl_fut_int, history.air_reserve, history.air_res_amt,history.oreo_bal, history.co_indicator,history.error_amt,history.err2,history.review_inc,history.lc_code, history.loan_type,history.special_note,history.collateral, history.property, history.total_coll_val, history.lendable_coll_val, history.regulatory, history.purpose,history.sic, history.shortname, history.ppna,history.iana,history.pana,history.parti_amt,history.sba_indicator,history.a_nxrtch, history.a_origdue, history.a_rtchfreq,history.a_comm, history.a_effdate,history.a_caps,history.a_margin,history.a_index,history.a_amort, history.a_pmt_code,history.restructure_ind, history.oacct_num, history.onote_num, history.serviced, history.admin_demand,history.kmc_due,history.serv_acct,history.kmc_eff_date,history.cap_costs, history.esc_amt, history.esc_bal, history.rest_chgoff, history.y2000,history.term, history.variance_da, history.p_i_pmt, history.aml_code,history.nxt_aml_an_da,history.plan_no, history.edit_date,history.sec_mtg_code,history.action_pla,history.recon_other_amt,history.recon_tc,history.act_restru,history.abt_status,history.non_recourse,history.extend_for_sale,history.seco_mtg,history.late_charges,history.high_ltv,history.high_env_risk,history.y2k_sent,history.amt_due,history.rate_code,history.hmda,history.fed,history.accreted,history.note_liab_code,history.note_loan_stat,history.recon_loan_stat,history.note_paid_ind,history.variable_plan,history.bill_type,history.past_due_amt,history.nxt_pmt_due_dte,history.loan_cat,history.pd_120,history.pd_15,history.gen_ledg_bal,history.chgoff,history.cocatnum,history.pi_freq,history.lnnuse1,history.nxt_pmt_chg_dte,history.acctclosedate,history.pmttermchg,history.forsale,history.pmt_freq,history.expt_mat,history.sale_pool,history.cmmt_nbr,history.int_purch,history.int_res,history.prior_15,history.prior_30,history.prior_60,history.prior_90,history.prior_120,history.non_acc_at_purch,history.fdic_classified,history.classified_date,history.loan_assumption,history.rltd_bnk_part_nbr,history.tdr,history.bnk_shr_accretion,history.assumed_ind,history.assumption_date,
history.GLAverage,history.cltv_glb,history.cltv_ni,history.ltv_glb,history.ltv_ni,history.ltv_autocomm,history.ltv_mancomm,history.ltv_mannibcltv,history.ltv_manglltv,history.ltv_manover,history.ltv_upbltv,history.ltv_upbcltv,history.ltv_manupbltv,history.frbnotl,history.watchlist,history.payoff,history.sbaloanproggrp,history.use_of_proceeds,history.opandi,history.origrate,history.ioterm,history.ltv_org_a,history.cltv_org_a,history.ltv_org_m,history.cltv_org_m,history.fradate,history.fpadate,history.securnt,history.ioenddt,history.note_mod_date,history.first_pay_date,history.num_collat,history.lastpymtfromsell,history.cnfrmlstpymtfromsell,history.secrdyloanops,history.secrdyassetmgr,history.fpercap,history.mpercap,history.aloanid,history.mtsid)
select tempHist.note_id, tempHist.as_of, tempHist.bisys_account,tempHist.bisys_note,tempHist.system, tempHist.officer,tempHist.status,tempHist.grade,tempHist.branch, tempHist.total_code, tempHist.purch_pool, tempHist.orig_amount, tempHist.orig_purch_bal, tempHist.current_balance, tempHist.accrued_in, tempHist.note_entry, tempHist.orig_date, tempHist.last_tran, tempHist.nxt_p_due, tempHist.nxt_i_due,tempHist.next_review,tempHist.nastatus,tempHist.orig_mat, tempHist.actual_mat,tempHist.est_mat,tempHist.closed,tempHist.interest_rate, tempHist.eff_rate, tempHist.rate_margin,tempHist.prime_ind, tempHist.prime_nx_ind, tempHist.prime_tier,tempHist.delay_ind, tempHist.delay, tempHist.delay_incr, tempHist.basis_code, tempHist.ceiling, tempHist.floor, tempHist.pmt_code, tempHist.bill_method, tempHist.monthly_pa, tempHist.prin_freq,tempHist.int_freq,tempHist.prin_due, tempHist.int_due, tempHist.pmt_amt, tempHist.pd_actual, tempHist.pd_report, tempHist.prior_pd_d, tempHist.pd_30, tempHist.pd_60,tempHist.pd_90,tempHist.prin_to_date,tempHist.pmtdiff, tempHist.adj_pmts,tempHist.settle_pmt,tempHist.amt_tfr,tempHist.ad, tempHist.ad_accretion,tempHist.ad_acc_to_date, tempHist.ad_xfer, tempHist.nad, tempHist.nad_accretion,tempHist.nad_acc_to_date, tempHist.nad_xfer, tempHist.dd_nad, tempHist.acc_status,tempHist.reg_co, tempHist.nad_co, tempHist.spec_reserve, tempHist.ttl_pmt_rec, tempHist.coll_fut_int, tempHist.uncoll_fut_int, tempHist.ttl_ad, tempHist.ttl_nad, tempHist.ttl_fut_int, tempHist.air_reserve, tempHist.air_res_amt,tempHist.oreo_bal, tempHist.bisys_co_indicator,tempHist.bisys_error_amt,tempHist.bisys_err2,tempHist.review_inc,tempHist.lc_code, tempHist.loan_type,tempHist.special_note,tempHist.collateral, tempHist.property, tempHist.total_coll_val, tempHist.lendable_coll_val, tempHist.regulatory, tempHist.purpose,tempHist.sic, tempHist.shortname, tempHist.ppna,tempHist.iana,tempHist.pana,tempHist.parti_amt,tempHist.sba_indicator,tempHist.a_nxrtch, tempHist.a_origdue, tempHist.a_rtchfreq,tempHist.a_comm, tempHist.a_effdate,tempHist.a_caps,tempHist.a_margin,tempHist.a_index,tempHist.a_amort, tempHist.a_pmt_code,tempHist.restructure_ind, tempHist.oacct_num, tempHist.onote_num, tempHist.serviced, tempHist.admin_demand,tempHist.kmc_due,tempHist.serv_acct,tempHist.kmc_eff_date,tempHist.cap_costs, tempHist.esc_amt, tempHist.esc_bal, tempHist.rest_chgoff, tempHist.y2000,tempHist.term, tempHist.variance_date, tempHist.p_i_pmt, tempHist.aml_code,tempHist.nxt_aml_an_da,tempHist.plan_no, tempHist.edit_date,tempHist.sec_mtg_code,tempHist.action_pla,tempHist.recon_other_amt,tempHist.recon_tc,tempHist.act_restru,tempHist.abt_status,tempHist.non_recourse,tempHist.extend_for_sale,tempHist.seco_mtg,tempHist.late_charges,tempHist.high_ltv,tempHist.high_env_risk,tempHist.y2k_sent,tempHist.amt_due,tempHist.rate_code,tempHist.hmda,tempHist.fed,tempHist.accreted,tempHist.note_liab_code,tempHist.note_loan_stat,tempHist.recon_loan_stat,tempHist.note_paid_ind,tempHist.variable_plan,tempHist.bill_type,tempHist.past_due_amt,tempHist.nxt_pmt_due_dte,tempHist.loan_cat,tempHist.pd_120,tempHist.pd_15,tempHist.gen_ledg_bal,tempHist.chgoff,tempHist.cocatnum,tempHist.pi_freq,tempHist.lnnuse1,tempHist.nxt_pmt_chg_dte,tempHist.acctclosedate,tempHist.pmttermchg,tempHist.forsale,tempHist.pmt_freq,tempHist.expt_mat,tempHist.sale_pool,tempHist.cmmt_nbr,tempHist.int_purch,tempHist.int_res,tempHist.prior_15,tempHist.prior_30,tempHist.prior_60,tempHist.prior_90,tempHist.prior_120,tempHist.non_acc_at_purch,tempHist.fdic_classified,tempHist.classified_date,tempHist.loan_assumption,tempHist.rltd_bnk_part_nbr,tempHist.tdr,tempHist.bank_shr_accretion,tempHist.assumed_ind,tempHist.assumption_date,tempHist.GLAverage,tempHist.cltv_glb,tempHist.cltv_ni,tempHist.ltv_glb,tempHist.ltv_ni,tempHist.ltv_autocomm,tempHist.ltv_mancomm,tempHist.ltv_mannibcltv,tempHist.ltv_manglltv,tempHist.ltv_manover,tempHist.ltv_upbltv,tempHist.ltv_upbcltv,tempHist.ltv_manupbltv,tempHist.frbnotl,tempHist.watchlist,tempHist.payoff,tempHist.sbaloanproggrp,tempHist.use_of_proceeds,tempHist.opandi,tempHist.origrate,tempHist.ioterm,tempHist.ltv_org_a,tempHist.cltv_org_a,tempHist.ltv_org_m,tempHist.cltv_org_m,tempHist.fradate,tempHist.fpadate,tempHist.securnt,tempHist.ioenddt,tempHist.note_mod_date,tempHist.first_pay_date,tempHist.num_collat,tempHist.lastpymtfromsell,tempHist.cnfrmlstpymtfromsell,tempHist.secrdyloanops,tempHist.secrdyassetmgr,tempHist.fpercap,tempHist.mpercap,tempHist.aloanid,tempHist.mtsid
FROM temphist LEFT JOIN history ON temphist.note_id = history.note_id
WHERE (history.as_of is null AND HISTORY.NOTE_ID IS NULL)
Any ideas?
Mary