Help in getting rid of temp tables in a query.

See more:

SQL

All,

I have the below query which makes use of multiple temp tables :

IF OBJECT_ID('RPTUSR.SP_Track_Record2') IS NOT NULL

BEGIN

    DROP PROCEDURE RPTUSR.SP_Track_Record2

    IF OBJECT_ID('RPTUSR.SP_Track_Record2') IS NOT NULL

        PRINT '<<< FAILED DROPPING PROCEDURE RPTUSR.SP_Track_Record2 >>>'

    ELSE

        PRINT '<<< DROPPED PROCEDURE RPTUSR.SP_Track_Record2 >>>'

END

go

CREATE PROCEDURE SP_Track_Record2

 

AS

BEGIN

 

                set compatibility_mode on

                select a.POLICY_NO,a.CERTIFICATE_NO,a.RENL_CERT_NO,a.EFF_DT_SEQ_NO,

                a.remarks_txt_id ,max (b.text_seq_no) text_seq_no ,

                text_seq_no1 = case when count(*) >1 then max (b.text_seq_no) -1 else 0 end,

                text_seq_no2 = case when count(*) >2 then max (b.text_seq_no) -2 else 0 end,

                text_seq_no3 = case when count(*) >3 then max (b.text_seq_no) -3 else 0 end,

                text_eff_date

                into #GetRemarkSeqNo

                from

                AUM_Reporting..Tpolicy a noholdlock,

                AUM_Reporting..ttext b noholdlock,

                G3..Track_Record_REPORT dd(index sla_policy_indx) noholdlock

                where

--             dd.accounting_period = 'Jul 31 2010'

                -- b.text_type_cd="4"   and  --(commented by amol041010)

                b.text_id = a.remarks_txt_id

                AND dd.POLICY_NO = a.POLICY_NO

                AND dd.CERTIFICATE_NO = a.CERTIFICATE_NO

                AND dd.RENL_CERT_NO = a.RENL_CERT_NO

                AND dd.EFF_DT_SEQ_NO = a.EFF_DT_SEQ_NO

                and convert(smalldatetime,convert(varchar,text_eff_date,111))>= convert(smalldatetime,convert(varchar,dd.WIP_CREATION_DT,111))

                and a.pol_office_cd = '064001'

                group by a.POLICY_NO,a.CERTIFICATE_NO,a.RENL_CERT_NO,a.EFF_DT_SEQ_NO,a.remarks_txt_id,b.text_eff_date

 

--47045 row(s) affected.

 

                CREATE INDEX WIP3in1_GetRemarkSeqNo  ON  #GetRemarkSeqNo (remarks_txt_id,text_seq_no,text_seq_no1,text_seq_no2,text_seq_no3 )

 

                --drop table #GetRemark3

                SELECT #getremarkseqno.POLICY_NO,#getremarkseqno.CERTIFICATE_NO,#getremarkseqno.RENL_CERT_NO,#getremarkseqno.EFF_DT_SEQ_NO,a.text_detail AS User_Remarks, a.text_type_cd,#getremarkseqno.text_eff_date

                into #GetRemark

                FROM #getremarkseqno,AUM_Reporting..ttext a

                where #getremarkseqno.remarks_txt_id = a.text_id

                AND #getremarkseqno.text_seq_no = a.text_seq_no

                --and a.text_type_cd="4"

 

--             CREATE INDEX WIP3in1_GetRemark  ON #GetRemark(WIP_NO)

                --GetRemark

 

                --GetRemark1

                SELECT #getremarkseqno.POLICY_NO,#getremarkseqno.CERTIFICATE_NO,#getremarkseqno.RENL_CERT_NO,#getremarkseqno.EFF_DT_SEQ_NO, a.text_detail AS User_Remarks1, a.text_type_cd,#getremarkseqno.text_eff_date

                into #GetRemark1

                FROM #getremarkseqno , AUM_Reporting..ttext a

                where #getremarkseqno.text_seq_no1 = a.text_seq_no

                AND #getremarkseqno.remarks_txt_id = a.text_id

                --and a.text_type_cd="4"

 

--             CREATE INDEX WIP3in1_GetRemark1  ON #GetRemark1(WIP_NO)

                --GetRemark1

 

                --GetRemark2

                SELECT #getremarkseqno.POLICY_NO,#getremarkseqno.CERTIFICATE_NO,#getremarkseqno.RENL_CERT_NO,#getremarkseqno.EFF_DT_SEQ_NO, a.text_detail AS User_Remarks2, a.text_type_cd,#getremarkseqno.text_eff_date

                into #GetRemark2

                FROM #getremarkseqno,AUM_Reporting..ttext a

                where #getremarkseqno.text_seq_no2 = a.text_seq_no

                AND #getremarkseqno.remarks_txt_id = a.text_id

                --and a.text_type_cd="4"

 

--             CREATE INDEX WIP3in1_GetRemark2  ON #GetRemark2(WIP_NO)

                --GetRemark2

 

 

--   UPDATE G3..Track_Record_REPORT

--   SET User_Remarks = b.User_Remarks

 

    /* Adaptive Server has expanded all '*' elements in the following statement */ select a.policy_no, a.certificate_no, a.renl_cert_no, a.eff_dt_seq_no, a.branch_off_cd, a.prdr_branch_sub_cd, a.Branch, a.product_name, a.major_line_cd, a.ProductGroup, a.pol_incept_date, a.pol_exp_date, a.ins_name_client_no, a.ClientName, a.Client_city_name, a.pol_status_cd, a.record_type_desc, a.policy_counter, a.accounting_period, a.ProducerName, a.PremiumAmount, a.o_prem_amt, a.SUM_INSURED, a.record_type_cd, a.producer_cd, a.EntryDate, a.EntryMonth, a.EntryDay, a.EntryWeek, a.InceptionMonth, a.InceptionDay, a.InceptionWeek, a.CoverNoteNumber, a.Cover_Note_date, a.Cov_nt_iss_month, a.Application_no, a.TIMESTAMP, a.watts_policy_no, a.Producer_type, a.times_renewed_cnt, a.Producer_Category, a.receipt_date, a.rcpt_branch_cd, a.rcpt_branch_sub_cd, a.receipt_no, a.product_cd, a.new_producer_cd, a.watts_issue_dt, a.Channel, a.prem_eff_date, a.orig_curr_cd, a.LOB, a.devel_det, a.PIN, a.WIP_NO, a.POL_TYPE, a.APPLICATION_RECEIVED_DT, a.WIP_CREATION_DT, a.POL_PRINT_DT, a.POL_DELIVERY_DT, a.QC_STATUS_CD, a.QC_REMARK_ID, a.QC_USERID_CD, a.QC_TIMESTAMP, a.QC_TIMES_REJECT, a.pol_chnge_eff_date, a.tpol_rcpt_dets, a.REFUNDEE_ID          , b.User_Remarks,b.text_eff_date into #Track_Record_report

   FROM #GetRemark B ,G3..Track_Record_REPORT A

   WHERE              b.POLICY_NO = a.POLICY_NO

                AND b.CERTIFICATE_NO = a.CERTIFICATE_NO

                AND b.RENL_CERT_NO = a.RENL_CERT_NO

                AND b.EFF_DT_SEQ_NO = a.EFF_DT_SEQ_NO

--             and accounting_period = 'Jul 31 2010'

 

 

--   UPDATE G3..Track_Record_REPORT

--   SET User_Remarks1 = b.User_Remarks1

 

                /* Adaptive Server has expanded all '*' elements in the following statement */ insert  into #Track_Record_report

                   select a.policy_no, a.certificate_no, a.renl_cert_no, a.eff_dt_seq_no, a.branch_off_cd, a.prdr_branch_sub_cd, a.Branch, a.product_name, a.major_line_cd,  a.ProductGroup, a.pol_incept_date, a.pol_exp_date, a.ins_name_client_no, a.ClientName, a.Client_city_name, a.pol_status_cd, a.record_type_desc, a.policy_counter, a.accounting_period, a.ProducerName, a.PremiumAmount, a.o_prem_amt, a.SUM_INSURED, a.record_type_cd, a.producer_cd, a.EntryDate, a.EntryMonth, a.EntryDay, a.EntryWeek, a.InceptionMonth, a.InceptionDay, a.InceptionWeek, a.CoverNoteNumber, a.Cover_Note_date, a.Cov_nt_iss_month, a.Application_no, a.TIMESTAMP, a.watts_policy_no, a.Producer_type, a.times_renewed_cnt, a.Producer_Category, a.receipt_date, a.rcpt_branch_cd, a.rcpt_branch_sub_cd, a.receipt_no, a.product_cd, a.new_producer_cd, a.watts_issue_dt, a.Channel, a.prem_eff_date, a.orig_curr_cd, a.LOB, a.devel_det, a.PIN, a.WIP_NO, a.POL_TYPE, a.APPLICATION_RECEIVED_DT, a.WIP_CREATION_DT, a.POL_PRINT_DT, a.POL_DELIVERY_DT, a.QC_STATUS_CD, a.QC_REMARK_ID, a.QC_USERID_CD, a.QC_TIMESTAMP, a.QC_TIMES_REJECT, a.pol_chnge_eff_date, a.tpol_rcpt_dets, a.REFUNDEE_ID                                                , b.User_Remarks1,b.text_eff_date

                   FROM #GetRemark1 B ,G3..Track_Record_REPORT A

                   WHERE              b.POLICY_NO = a.POLICY_NO

                AND b.CERTIFICATE_NO = a.CERTIFICATE_NO

                AND b.RENL_CERT_NO = a.RENL_CERT_NO

                AND b.EFF_DT_SEQ_NO = a.EFF_DT_SEQ_NO

--             and accounting_period = 'Jul 31 2010'

 

--   UPDATE G3..Track_Record_REPORT

--   SET User_Remarks2 = b.User_Remarks2

 

    /* Adaptive Server has expanded all '*' elements in the following statement */ insert  into #Track_Record_report

   select a.policy_no, a.certificate_no, a.renl_cert_no, a.eff_dt_seq_no, a.branch_off_cd, a.prdr_branch_sub_cd, a.Branch, a.product_name, a.major_line_cd, a.ProductGroup,  a.pol_incept_date, a.pol_exp_date, a.ins_name_client_no, a.ClientName, a.Client_city_name, a.pol_status_cd, a.record_type_desc, a.policy_counter, a.accounting_period, a.ProducerName, a.PremiumAmount, a.o_prem_amt, a.SUM_INSURED, a.record_type_cd, a.producer_cd, a.EntryDate, a.EntryMonth, a.EntryDay, a.EntryWeek, a.InceptionMonth, a.InceptionDay, a.InceptionWeek, a.CoverNoteNumber, a.Cover_Note_date, a.Cov_nt_iss_month, a.Application_no, a.TIMESTAMP, a.watts_policy_no, a.Producer_type, a.times_renewed_cnt, a.Producer_Category, a.receipt_date, a.rcpt_branch_cd, a.rcpt_branch_sub_cd, a.receipt_no, a.product_cd, a.new_producer_cd, a.watts_issue_dt, a.Channel, a.prem_eff_date, a.orig_curr_cd, a.LOB, a.devel_det, a.PIN, a.WIP_NO, a.POL_TYPE, a.APPLICATION_RECEIVED_DT, a.WIP_CREATION_DT, a.POL_PRINT_DT, a.POL_DELIVERY_DT, a.QC_STATUS_CD, a.QC_REMARK_ID, a.QC_USERID_CD, a.QC_TIMESTAMP, a.QC_TIMES_REJECT, a.pol_chnge_eff_date, a.tpol_rcpt_dets, a.REFUNDEE_ID                                               , b.User_Remarks2,b.text_eff_date

   FROM #GetRemark2 B ,G3..Track_Record_REPORT A

   WHERE              b.POLICY_NO = a.POLICY_NO

                AND b.CERTIFICATE_NO = a.CERTIFICATE_NO

                AND b.RENL_CERT_NO = a.RENL_CERT_NO

                AND b.EFF_DT_SEQ_NO = a.EFF_DT_SEQ_NO

-- and    accounting_period = 'Jul 31 2010'

 

 

 

IF OBJECT_ID('tempdb..Track_Record') IS NOT NULL

BEGIN

    DROP TABLE tempdb..Track_Record

END

 

 

/* Adaptive Server has expanded all '*' elements in the following statement */ select #Track_Record_report.policy_no, #Track_Record_report.certificate_no, #Track_Record_report.renl_cert_no, #Track_Record_report.eff_dt_seq_no, #Track_Record_report.branch_off_cd, #Track_Record_report.prdr_branch_sub_cd, #Track_Record_report.Branch, #Track_Record_report.product_name, #Track_Record_report.major_line_cd,  #Track_Record_report.ProductGroup,  #Track_Record_report.pol_incept_date, #Track_Record_report.pol_exp_date, #Track_Record_report.ins_name_client_no, #Track_Record_report.ClientName, #Track_Record_report.Client_city_name, #Track_Record_report.pol_status_cd, #Track_Record_report.record_type_desc, #Track_Record_report.policy_counter, #Track_Record_report.accounting_period, #Track_Record_report.ProducerName, #Track_Record_report.PremiumAmount, #Track_Record_report.o_prem_amt, #Track_Record_report.SUM_INSURED, #Track_Record_report.record_type_cd, #Track_Record_report.producer_cd, #Track_Record_report.EntryDate, #Track_Record_report.EntryMonth, #Track_Record_report.EntryDay, #Track_Record_report.EntryWeek, #Track_Record_report.InceptionMonth, #Track_Record_report.InceptionDay, #Track_Record_report.InceptionWeek, #Track_Record_report.CoverNoteNumber, #Track_Record_report.Cover_Note_date, #Track_Record_report.Cov_nt_iss_month, #Track_Record_report.Application_no, #Track_Record_report.TIMESTAMP, #Track_Record_report.watts_policy_no, #Track_Record_report.Producer_type, #Track_Record_report.times_renewed_cnt, #Track_Record_report.Producer_Category, #Track_Record_report.receipt_date, #Track_Record_report.rcpt_branch_cd, #Track_Record_report.rcpt_branch_sub_cd, #Track_Record_report.receipt_no, #Track_Record_report.product_cd, #Track_Record_report.new_producer_cd, #Track_Record_report.watts_issue_dt, #Track_Record_report.Channel, #Track_Record_report.prem_eff_date, #Track_Record_report.orig_curr_cd, #Track_Record_report.LOB, #Track_Record_report.devel_det, #Track_Record_report.PIN, #Track_Record_report.WIP_NO, #Track_Record_report.POL_TYPE, #Track_Record_report.APPLICATION_RECEIVED_DT, #Track_Record_report.WIP_CREATION_DT, #Track_Record_report.POL_PRINT_DT, #Track_Record_report.POL_DELIVERY_DT, #Track_Record_report.QC_STATUS_CD, #Track_Record_report.QC_REMARK_ID, #Track_Record_report.QC_USERID_CD, #Track_Record_report.QC_TIMESTAMP, #Track_Record_report.QC_TIMES_REJECT, #Track_Record_report.pol_chnge_eff_date, #Track_Record_report.tpol_rcpt_dets, #Track_Record_report.REFUNDEE_ID, #Track_Record_report.User_Remarks, #Track_Record_report.text_eff_date into tempdb..Track_Record  from #Track_Record_report

 

delete  from    tempdb..Track_Record where user_remarks like  '%clean%'

 

end

 

 

 

 

 

go

IF OBJECT_ID('RPTUSR.SP_Track_Record2') IS NOT NULL

    PRINT '<<< CREATED PROCEDURE RPTUSR.SP_Track_Record2 >>>'

ELSE

    PRINT '<<< FAILED CREATING PROCEDURE RPTUSR.SP_Track_Record2 >>>'

Now I want to get rid of all the temporary tables in the above query :

Below is the query that I have written, but it does not return any rows. Can anyone help?

What I have tried:

select a.policy_no, a.certificate_no, a.renl_cert_no, a.eff_dt_seq_no, a.branch_off_cd, a.prdr_branch_sub_cd, a.Branch, a.product_name, a.major_line_cd,

 a.ProductGroup, a.pol_incept_date, a.pol_exp_date, a.ins_name_client_no, a.ClientName, a.Client_city_name, a.pol_status_cd, a.record_type_desc,

 a.policy_counter, a.accounting_period, a.ProducerName, a.PremiumAmount, a.o_prem_amt, a.SUM_INSURED, a.record_type_cd, a.producer_cd, a.EntryDate,

  a.EntryMonth, a.EntryDay, a.EntryWeek, a.InceptionMonth, a.InceptionDay, a.InceptionWeek, a.CoverNoteNumber, a.Cover_Note_date, a.Cov_nt_iss_month,

  a.Application_no, a.TIMESTAMP, a.watts_policy_no, a.Producer_type, a.times_renewed_cnt, a.Producer_Category, a.receipt_date, a.rcpt_branch_cd,

  a.rcpt_branch_sub_cd, a.receipt_no, a.product_cd, a.new_producer_cd, a.watts_issue_dt, a.Channel, a.prem_eff_date, a.orig_curr_cd, a.LOB, a.devel_det,

   a.PIN, a.WIP_NO, a.POL_TYPE, a.APPLICATION_RECEIVED_DT, a.WIP_CREATION_DT, a.POL_PRINT_DT, a.POL_DELIVERY_DT, a.QC_STATUS_CD, a.QC_REMARK_ID,

    a.QC_USERID_CD, a.QC_TIMESTAMP, a.QC_TIMES_REJECT, a.pol_chnge_eff_date, a.tpol_rcpt_dets, a.REFUNDEE_ID, b.text_eff_date

    --b.User_Remarks1,b.text_eff_date,  a.Entry

 

  FROM

  (select x.POLICY_NO,x.CERTIFICATE_NO,x.RENL_CERT_NO,x.EFF_DT_SEQ_NO,

                x.remarks_txt_id ,max (y.text_seq_no) text_seq_no ,

                text_seq_no1 = case when count(*) >1 then max (y.text_seq_no) -1 else 0 end,

                text_seq_no2 = case when count(*) >2 then max (y.text_seq_no) -2 else 0 end,

                text_seq_no3 = case when count(*) >3 then max (y.text_seq_no) -3 else 0 end,

                text_eff_date

               

                from

                AUM_Reporting..Tpolicy x noholdlock,

                AUM_Reporting..ttext y noholdlock,

                S3REPORTS..Track_Record dd(index sla_policy_indx) noholdlock

                where

--             dd.accounting_period = 'Jul 31 2010'

                -- b.text_type_cd="4"   and  --(commented by amol041010)

                y.text_id = x.remarks_txt_id

                AND dd.POLICY_NO = x.POLICY_NO

                AND dd.CERTIFICATE_NO = x.CERTIFICATE_NO

                AND dd.RENL_CERT_NO = x.RENL_CERT_NO

                AND dd.EFF_DT_SEQ_NO = x.EFF_DT_SEQ_NO

                and convert(smalldatetime,convert(varchar,text_eff_date,111))>= convert(smalldatetime,convert(varchar,dd.WIP_CREATION_DT,111))

                and x.pol_office_cd = '064001'

                group by x.POLICY_NO,x.CERTIFICATE_NO,x.RENL_CERT_NO,x.EFF_DT_SEQ_NO,x.remarks_txt_id,y.text_eff_date)

                                      

                         B ,

                                  

                   S3REPORTS..Track_Record A,

                  

                   AUM_Reporting..ttext C

                  

                   WHERE              b.POLICY_NO = a.POLICY_NO

                AND b.CERTIFICATE_NO = a.CERTIFICATE_NO

                AND b.RENL_CERT_NO = a.RENL_CERT_NO

                AND b.EFF_DT_SEQ_NO = a.EFF_DT_SEQ_NO

               

                AND b.remarks_txt_id = C.text_id

                AND b.text_seq_no = C.text_seq_no
                AND b.text_seq_no1 = C.text_seq_no

                AND b.text_seq_no2 = C.text_seq_no