Reputation: 13
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
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