帮助摆脱查询中的临时表。

本文介绍了帮助摆脱查询中的临时表。的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 全部， 我有以下查询，它使用多个临时表： 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 ' <<<失败的删除程序RPTUSR.SP_Track_Record2>>>' ELSE PRINT ' <<<删除程序RPTUSR.SP_Track_Record2>>>' END go CREATE PROCEDURE SP_Track_Record2 AS BEGIN set compatibility_mode on 选择 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 何时 count（*）> 1 然后 max（b.text_seq_no）-1 else 0 end ， text_seq_no2 = case 当 count（*）> 2 然后 max（b.text_seq_no）-2 else 0 end ， text_seq_no3 = case 当 count（*）> 3 然后 max（b.text_seq_no） - 3 else 0 end ， text_eff_date into #GetRemarkSeqNo 来自 AUM_Reporting..Tpolicy a noholdlock， AUM_Reporting..ttext b noholdlock， G3 .. Track_Record_REPORT dd（ index sla_policy_indx）noholdlock 其中 - dd.accounting_period ='2010年7月31日' - b.text_type_cd =4and - （由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 和 convert （ smalldatetime ， convert （ varchar ，text_eff_date， 111 ））> = convert （ smalldatetime ， convert （ varchar ，dd.WIP_CREATION_DT， 111 ）） 和 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行受影响。 创建 INDE X 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 其中＃getremarkseqno.remarks_txt_id = a.text_id AND #getremarkseqno.text_seq_no = a.text_seq_no - 和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 其中＃getremarkseqno.text_seq_no1 = a.text_seq_no AND ＃getremarkseqno.remarks_txt_id = a.text_id - 和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_dat e into #GetRemark2 FROM ＃ getremarkseqno，AUM_Reporting..ttext 其中＃getremarkseqno.text_seq_no2 = a.text_seq_no AND #getremarkseqno.remarks_txt_id = a.text_id - 和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扩展了以下语句中的所有'*'元素* / 选择 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 - 和accounting_period ='2010年7月31日' - 更新G3..Track_Record_REPORT - SET User_Remarks1 = b.User_Remarks1 / * Adaptive Server扩展了所有' *'以下语句中的元素* / 插入 进入 #Track_Record_report 选择 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.ClientNam e，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.tex t_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 - 和accounting_period ='2010年7月31日' - 更新G3..Track_Record_REPORT - SET User_Remarks2 = b.User_Remarks2 / * A daptive Server已在以下语句中扩展了所有'*'元素* / insert 进入＃ Track_Record_report 选择 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 - 和accounting_period ='2010年7月31日' IF OBJECT_ID （' tempdb..Track_Record'） IS 不 NULL BEGIN DROP TABLE tempdb..Track_Record END / * Adaptive Server扩展了以下语句中的所有'*'元素* / 选择＃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_R ecord_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 来自 tempdb..Track_Record 其中 user_remarks 喜欢 ' ％clean％' end go IF OBJECT_ID （' RPTUSR.SP_Track_Record2'） IS NOT NULL PRINT ' <<<创建过程RPTUSR.SP_Track_Record2>>>' ELSE PRINT ' <<<失败的创建程序RPTUSR.SP_Track_Record2>>>' 现在我想摆脱所有暂时的上面查询中的表： 下面是我编写的查询，但它不返回任何行。有人可以帮忙吗？ 我尝试过： 选择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_ef f_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 （选择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 =当count（*）> 1然后是max（y）时的情况.text_seq_no）-1 else 0 end， text_seq_no2 = count（*）> 2的情况然后max（y.text_seq_no）-2 else 0 end， text_seq_no3 = count（*）> 3时的情况，然后是max（y.text_seq_no）-3 else 0 end， text_eff_date 来自 AUM_Reporting..Tpolicy x noholdlock， AUM_Reporting..ttext y noholdlock， S3REPORTS..Track_Record dd（index sla_policy_indx）noholdlock 其中 - dd.accounting_period ='2010年7月31日' - b.text_type_cd =4和 - （评论人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 和convert（smalldatetime，convert（varchar，text_eff_date， 111））> =转换（smalldatetime，convert（varchar，dd.WIP_CREATION_DT，111）） 和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 解决方案 SQL Server中的临时表简介 [ ^ ] 那里有一节关于删除临时表的内容 All,I have the below query which makes use of multiple temp tables :IF OBJECT_ID('RPTUSR.SP_Track_Record2') IS NOT NULLBEGIN 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 >>>'ENDgoCREATE PROCEDURE SP_Track_Record2 ASBEGIN 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 NULLBEGIN DROP TABLE tempdb..Track_RecordEND /* 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 goIF 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 解决方案 Introduction to Temporary Tables in SQL Server[^]There is a section in there about deleting temporary tables 这篇关于帮助摆脱查询中的临时表。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！