how do i drop a tmp table

[sonny1974]

here is my query, when i run it the second time , it says #tmp1 is there already and i get an error. is there a way to drop the table after the report is run .

SELECT CS_ID, PGM_TYP_CD, MAX(UPD_DTM) AS selectedUPD_DTM
INTO #TMP1
FROM cis.dbo.CS_PGM
WHERE (EFF_END_DT IS NULL)
GROUP BY CS_ID, PGM_TYP_CD


select *
from cis.dbo.cs_pgm pgm
inner join cis.dbo.se_csld csld
on pgm.csld_id = csld.csld_id
inner join cis.dbo.bi_dtl bi
on pgm.cs_id = bi.cs_id and pgm.pgm_typ_cd = bi.pgm_typ_cd
inner join cis.dbo.rt_pick_locn locn
on bi.iss_site_cd = locn.pick_locn_cd
inner join cis.dbo.#TMP1
on pgm.cs_id = #TMP1.cs_id and
pgm.pgm_typ_cd = #TMP1.pgm_typ_cd and
pgm.upd_dtm = #TMP1.selectedUPD_DTM

where
PGM.PGM_TYP_CD = 'GA' and
PGM.EFF_END_DT is null and
--{BI.BEN_BGN_DT} = {?Benefit Begin Date} and
LOCN.EFF_END_DT is null and
BI.USE_AMT > 0 and
LOCN.PICK_LOCN_TYP = 'V'


SELECT CS_ID, PGM_TYP_CD, MAX(UPD_DTM) AS selectedUPD_DTM
INTO #TMP1
FROM cis.dbo.CS_PGM
WHERE (EFF_END_DT IS NULL)
GROUP BY CS_ID, PGM_TYP_CD


select *
from cis.dbo.cs_pgm pgm
inner join cis.dbo.se_csld csld
on pgm.csld_id = csld.csld_id
inner join cis.dbo.bi_dtl bi
on pgm.cs_id = bi.cs_id and pgm.pgm_typ_cd = bi.pgm_typ_cd
inner join cis.dbo.rt_pick_locn locn
on bi.iss_site_cd = locn.pick_locn_cd
inner join cis.dbo.#TMP1
on pgm.cs_id = #TMP1.cs_id and
pgm.pgm_typ_cd = #TMP1.pgm_typ_cd and
pgm.upd_dtm = #TMP1.selectedUPD_DTM

where
PGM.PGM_TYP_CD = 'GA' and
PGM.EFF_END_DT is null and
--{BI.BEN_BGN_DT} = {?benefit_beg_date} and
LOCN.EFF_END_DT is null and
BI.USE_AMT > 0 and
LOCN.PICK_LOCN_TYP = 'V'

 

[SQLSister]

drop table #temp

Questions about posting. See faq183-874

 

[sonny1974]

where do i add this?

 

[sonny1974]

i get an error when i put it on the bottem


(SELECT CS_ID, PGM_TYP_CD, MAX(UPD_DTM) AS-selectedUPD_DTM
INTO #TMP1
FROM cis.dbo.CS_PGM
WHERE (EFF_END_DT IS NULL)
GROUP BY CS_ID, PGM_TYP_CD


select *
from cis.dbo.cs_pgm pgm
inner join cis.dbo.se_csld csld
on pgm.csld_id = csld.csld_id
inner join cis.dbo.bi_dtl bi
on pgm.cs_id = bi.cs_id and pgm.pgm_typ_cd = bi.pgm_typ_cd
inner join cis.dbo.rt_pick_locn locn
on bi.iss_site_cd = locn.pick_locn_cd
inner join cis.dbo.#TMP1
on pgm.cs_id = #TMP1.cs_id and
pgm.pgm_typ_cd = #TMP1.pgm_typ_cd and
pgm.upd_dtm = #TMP1.selectedUPD_DTM

where
PGM.PGM_TYP_CD = 'GA' and
PGM.EFF_END_DT is null and
--{BI.BEN_BGN_DT} = {?Benefit Begin Date} and
LOCN.EFF_END_DT is null and
BI.USE_AMT > 0 and
LOCN.PICK_LOCN_TYP = 'V')

drop table #TMP1

 

[sonny1974]

i am using sql server and crystal xi

 

[SQLSister]

Well there are syntax errors it the other code you posted, that could be why. What error are you getting?

Questions about posting. See faq183-874

 

[Lamprey13]

I also like to program defensivly, so that I'd have an error block at the bottom to clean up my objects (like temp tables). Either way you could check for the existance of the temp table and then drop it before you SELECT INTO it.

IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL 
        DROP TABLE #TEMP

-Ryan

 

[drek01]

Did you get it done or not? if not try this.

if exists (select * from sysobjects where id = object_id('[#tmp1'))
		DROP TABLE [tmp1]