CODEWITHSUNDEEP
sql
DIFF1
DIFF1

Reputation: 13

Difficulty selecting a temporary table

I have created a temporary table

SELECT l.CMAGY AS 'AGENCY', l.cmschn AS 'MASTER_CHAIN', l.CMCHN AS 'CHAIN', p.PDSTR AS 'STORE NUMBER', p.PDWEDT AS 'WK_ENDING',
p.PDQSLD AS 'POS_UNITS',
p.PDQSLD * p.PDRSP AS 'POS_BILLING',
p.PDQSLD * p.PDRPRC AS 'POS_MSRP',
p.PDQOH AS 'Retailer_on_hand_units'

**INTO #temp2**
FROM REPIT.LEVYDTA.POSDTLM p
JOIN REPIT.LEVYDTA.LDSCHNM l
ON p.PDAGY= l.CMAGY AND p.PDCHN = l.CMCHN
Where p.pdwedt = 20210731;

It seems to be created. The message says "(5914766 rows affected)

Completion time: 2021-08-04T08:07:08.9727872-07:00"

After that, I try to select it and it says "invalid object"

SELECT *
FROM #temp2;

I receive such an error: "Msg 208, Level 16, State 0, Line 1 Invalid object name '#temp2'.

Completion time: 2021-08-04T08:08:05.3919314-07:00"

Does anyone know what is wrong with selecting a temporary table?

Upvotes: 1

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

A temporary table exists only in the session where you create it. So, you have to be sure that you create it in the same session.

You can create a global temporary table instead if you need to access the table from another session. These are prefixed with ## instead of #:

select . . . 
into ##temp1

Alternatively, you might want a real table.

Upvotes: 1

Related Questions