trying to create a temp table in my sql

[sonny1974]

i have ms sql server and crystal reports xi

i have created temp tables before but i was using oracle

below is my temp table, when i add this to my sql i get an error. how would i add this into my query using ms sql server

with qry1 as (
SELECT cis.dbo.CS.CS_ID, cis.dbo.RT_SPL_CHAR.SPL_CHAR_DESC, cis.dbo.CS_SPL_SITN.EFF_BGN_DT AS SpecialIndicatorBeginDate,
cis.dbo.CS_SPL_SITN.EFF_END_DT AS SpecialIndicatorEndDate, DATEADD([month], 2, cis.dbo.CS_SPL_SITN.EFF_BGN_DT)
AS SpecialIndicatorCompareDate
FROM cis.dbo.CS INNER JOIN
cis.dbo.CS_SPL_SITN ON cis.dbo.CS.CS_ID = cis.dbo.CS_SPL_SITN.CS_ID INNER JOIN
cis.dbo.RT_SPL_CHAR ON cis.dbo.CS_SPL_SITN.SITN_TYP_CD = cis.dbo.RT_SPL_CHAR.SPL_CHAR_CD
WHERE (cis.dbo.CS.EFF_END_DT IS NULL) AND (cis.dbo.CS.HIST_IND IS NULL) AND (cis.dbo.CS.CS_STS_CD = 'OP') AND (DATEADD([month], 2,
cis.dbo.CS_SPL_SITN.EFF_BGN_DT) < GETDATE()) AND (ISNULL(cis.dbo.CS_SPL_SITN.EFF_END_DT, DATEADD([day], 1, GETDATE())) > GETDATE()) AND
(cis.dbo.RT_SPL_CHAR.SPL_CHAR_DESC = 'E - Employed'))

 

[ptheriault]

To create a temp table use #TableName. This will create a temp table that is available for the current process only. If you need to make the temp table available to other processes use ##TableName

So you would have something like this.

Create #tmp (CS_ID int,
RT_SPL_CHAR varchar(100),
.....etc
)

You could also create the table from the insert.
Like this

SELECT ...
INTO #tmp
FROM ...
WHERE ...

Something else you should look up in books on-line is table variables. I've gotten better performance with them in the past.
The syntax for a table variable is.

declare @table TABLE(CS_ID int,
RT_SPL_CHAR varchar(100),
...etc)


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[sonny1974]

so this would go into my sql with the rest of my sql

 

[ptheriault]

yes,
You would create your temp table then perform an insert into your temp table.

Here is a small example

create #tmp (id int
,name varchar(30))

insert into #tmp(id,name)
select id, name
from myTable
where ...

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[sonny1974]

so like in my example i can just add a query in my sql and make it a temp table, without having to do below. i was using oracle 9i before

(CS_ID int,
RT_SPL_CHAR varchar(100),
.....etc

I cant just creat a query and use it as a temp table

 

[ptheriault]

Do it like this and you will have a temp table name #tmp that you can use for other queries.

SELECT     cis.dbo.CS.CS_ID, cis.dbo.RT_SPL_CHAR.SPL_CHAR_DESC, cis.dbo.CS_SPL_SITN.EFF_BGN_DT AS SpecialIndicatorBeginDate,
                     cis.dbo.CS_SPL_SITN.EFF_END_DT AS SpecialIndicatorEndDate, DATEADD([month], 2, cis.dbo.CS_SPL_SITN.EFF_BGN_DT)
                      AS SpecialIndicatorCompareDate
[b]INTO #TMP[/b]
FROM         cis.dbo.CS INNER JOIN
                      cis.dbo.CS_SPL_SITN ON cis.dbo.CS.CS_ID = cis.dbo.CS_SPL_SITN.CS_ID INNER JOIN
                      cis.dbo.RT_SPL_CHAR ON cis.dbo.CS_SPL_SITN.SITN_TYP_CD = cis.dbo.RT_SPL_CHAR.SPL_CHAR_CD
WHERE     (cis.dbo.CS.EFF_END_DT IS NULL) AND (cis.dbo.CS.HIST_IND IS NULL) AND (cis.dbo.CS.CS_STS_CD = 'OP') AND (DATEADD([month], 2,
                      cis.dbo.CS_SPL_SITN.EFF_BGN_DT) < GETDATE()) AND (ISNULL(cis.dbo.CS_SPL_SITN.EFF_END_DT, DATEADD([day], 1, GETDATE())) > GETDATE()) AND
                      (cis.dbo.RT_SPL_CHAR.SPL_CHAR_DESC = 'E - Employed')

- Paul  [img]http://i19.tinypic.com/10qkyfp.gif[/img]
- If at first you don't succeed, find out if the loser gets anything.

 

[sonny1974]

when i try to run the report again, they say its #tmp is already there.

how can i have it set to whenever it runs it refreshes the temp table also

 

[sonny1974]

is there a way to drop the table so when the next person that runs the report will have the refreshed 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'

 

[Lamprey13]

You can simply drop the table and/or you can put a check in to make sure it exists before you try dropping it:

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