Why isn't my Create Table query working

1

Hello all

I am trying to create a new table in my db but keep getting an error. What am I doing wrong? Here is the code I am trying to use.

	``Create table [phm_db].[dbo].[pcp2] as
Select Distinct
   MEMBERLINKEID
, LINKINGPROVIDERID
, ATTRIBUTEDPCPPROVIDERID_LINKAGE_ID
, MEMBERPCPID_LINKAGE_ID
, pv.NATIONALPROVIDERIDCLEANED as PCP_NPI
, pv.ORGID
, 'PCP' as ReferralType
, SUBMISSIONYEAR as me_submissionyear
, '2018' as SubmissionYear_new

From [phm_db].[dbo].[340_DFCI_Chaudhuri_MemberEligibility] me 
Left Join [phm_db].[dbo].[340_DFCI_Chaudhuri_Provider] pv On me.MEMBERPCPID_LINKAGE_ID = pv.LINKINGPROVIDERID and me.ORGID = pv.ORGID
Where 1=1
	  --AND me.SUBMISSIONYEAR = 2014
	  AND SUBMISSIONYEAR = 2018
	  --AND me.SUBMISSIONMONTH IN (1,2,3)
	  AND pv.NATIONALPROVIDERIDCLEANED IS NOT NULL``
2

Hello,

Is this on Microsoft SQL Server?

3

yes it is

4

this is one way but it is not good, because next time it runs it will complain the table already exists. Are you doing this just for one time to see the result?

YOUR_SELECT_HERE
into [phm_db].[dbo].[pcp2]    --<<<
From [phm_db].[dbo].[340_DFCI_Chaudhuri_MemberEligibility] me 
Left Join [phm_db].[dbo].[340_DFCI_Chaudhuri_Provider] pv On me.MEMBERPCPID_LINKAGE_ID = pv.LINKINGPROVIDERID and me.ORGID = pv.ORGID
Where 1=1

--etc etc etc
1 Like
5

I would be using it more than once that's why I didn't want to make a temp table. It takes too long to run

6

You don't have to create a temp table - you can create a permanent table using INTO:

  DROP TABLE IF EXISTS dbo.MyNewTable;

SELECT ...
  INTO dbo.MyNewTable
  FROM ...
 WHERE ...

You can then add a clustered index on an appropriate column and any non-clustered indexes as needed to support your queries.

1 Like
7

Thanks Josiasz and Jeff, sorry for the late response...that worked perfectly