Can you alias a temp Table? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Can you alias a temp Table?

Author

Topic

kohlhaas77
Starting Member

25 Posts

Posted - 2008-07-21 : 11:37:12

Here is the syntax, but when I attempt to alias it, I get "Incorrect syntax near 'S1'".

SELECT *
INTO #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1 S1
FROM MAHP_Recon_Reports.dbo.PNCG0_Extract PE
WHERE (PE.CANC_DT <> PE.EFF_DT)
AND (PE.CONTR_TYP_CD = 'H')
AND (PE.CANC_DT > GETDATE())
AND (PE.PROV_TYP_CD = 'P')
AND (PE.IPA_NBR IN (31,32,33,34,35,36,37,38,39,40,41,42,43,
44,45,46,47,48,49,50))

Thanks in advance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 11:44:28

What is the purpose of aliasing your target table? you dont need alias there.

kohlhaas77
Starting Member

25 Posts

Posted - 2008-07-21 : 11:47:45

..because when I refer to it later, I am forced to use the full temp table name...

SELECT *
--INTO Table 2
FROM
(SELECT #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.PROV_ID, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.TAX_ID_NBR, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.ACPT_PTNT_CD, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.CANC_DT, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.CONTR_ARNG_CD, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.EFF_DT, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.FEE_SCHED_NBR, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.IPA_NBR,
CASE WHEN #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.IPA_NBR IN (1,8,11,37) THEN 'SET1'
WHEN #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.IPA_NBR IN (2,10,230,500) THEN 'SET2'
ELSE 'NULLSET'
END AS SETVALUE,
#MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.MKT_NBR, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.PAY_METH_CD, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.PRDCT_OFR_ID, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.PROV_CONTR_ROLE_CD, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.CONTR_ID, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.PROV_TYP_CD, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.ORG_TYP_CD, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.LST_UPDT_DT,
#MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.CREATE_DATE, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.MASTER_ID, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.SPECIALTY_CODE, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.SPECIALTY_PRIMARY_IND, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.SPECIALTY_DESC
FROM #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1) T
WHERE T.SETVALUE <> 'NULLSET'

I am fairly new to SQL, so I know I am missing something here.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 13:56:54

quote:
Originally posted by kohlhaas77

..because when I refer to it later, I am forced to use the full temp table name...

SELECT *
--INTO Table 2
FROM
(SELECT #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.PROV_ID, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.TAX_ID_NBR, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.ACPT_PTNT_CD, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.CANC_DT, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.CONTR_ARNG_CD, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.EFF_DT, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.FEE_SCHED_NBR, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.IPA_NBR,
CASE WHEN #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.IPA_NBR IN (1,8,11,37) THEN 'SET1'
WHEN #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.IPA_NBR IN (2,10,230,500) THEN 'SET2'
ELSE 'NULLSET'
END AS SETVALUE,
#MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.MKT_NBR, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.PAY_METH_CD, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.PRDCT_OFR_ID, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.PROV_CONTR_ROLE_CD, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.CONTR_ID, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.PROV_TYP_CD, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.ORG_TYP_CD, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.LST_UPDT_DT,
#MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.CREATE_DATE, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.MASTER_ID, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.SPECIALTY_CODE, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.SPECIALTY_PRIMARY_IND, #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1.SPECIALTY_DESC
FROM #MHP001_14_StandardPCPIPACrossing_RadsMarkets_1) T
WHERE T.SETVALUE <> 'NULLSET'

I am fairly new to SQL, so I know I am missing something here.

here you will need an alias, but the code you posted early was where you popluate the temporary table. what was purpose of using alias here?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-21 : 14:06:58

You can't alias it in the INTO. You can alias it once you query it though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources