Create New Table From Query Results

I am attempting to create a new table from the results a query. I've attempted select into, also have attempted create table. I've attempted select into, also have attempted crea

Solution 1:

SQL Server 2014 does not support CTAS syntax. You could use SELECT ... INTO instead:

select *   -- * is antipattern and columns should be explicitly listed
into InitialJoinwithCPCL
from [dbo].[Combined] as a
left join [dbo].[CPCL] as b
    on a.[StateAbbr] = b.[ST] and a.[CropName] = b.[CROPNAME]
where cropyear <> 2019and (policynumber isnotnull) 
and (PolicyAcres <> 0) and (Policyliability <> 0or PolicyAcres <= 0) and (Endorsement isnull)

Solution 2:

You should first Create your table then try to insert data into it. try something like this:

CREATETABLE InitialJoinwithCPCL ( [Id] bigint, [Name] nvarchar(max), .... )
INSERTINTO InitialJoinwithCPCL
SELECT*FROM [dbo].[Combined] as a
LEFTJOIN [dbo].[CPCL] as b
on a.[StateAbbr] = b.[ST] and a.[CropName] = b.[CROPNAME]
WHERE cropyear <>2019and (policynumber isnotnull) 
AND (PolicyAcres <>0) and (Policyliability <>0or PolicyAcres <=0) AND 
(Endorsement isnull)

make sure data type provided by your select statement is same as the table you will create.