Create new table from query results

Question

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 create table.

This is my original code, I am attempting to create a new table from the output of this code called 'InitialJoinwithCPCL'

select *
from [dbo].[Combined] as a
left join [dbo].[CPCL] as b
    on a.[StateAbbr] = b.[ST] and a.[CropName] = b.[CROPNAME]
where cropyear <> 2019 and (policynumber is not null) 
and (PolicyAcres <> 0) and (Policyliability <> 0 or PolicyAcres <= 0) and (Endorsement is null)

I have attempted this but get this error, 'Incorrect syntax near '('.'

create table InitialJoinwithCPCL
as (select * 
from [dbo].[Combined] as a
left join [dbo].[CPCL] as b
    on a.[StateAbbr] = b.[ST] and a.[CropName] = b.[CROPNAME]
where cropyear <> 2019 and (policynumber is not null) 
and (PolicyAcres <> 0) and (Policyliability <> 0 or PolicyAcres <= 0) and (Endorsement is null));

You can't combine DDL and DML statements. Use `SELECT ... INTO` instead. — Eric, May 03 '19 at 16:01
Is this just an ad-hoc query or production code? As others have said you cannot have multiple columns with the same name. Also, consider how you will use this. If you do get it to run and try and run it a second time it will fail because the table already exists. So you either need to drop the table before you run your SELECT ... INTO statement or create your table once and then insert into and delete from it however you need to. — Isaac, May 07 '19 at 04:52

score 1 · Answer 1 · answered May 03 '19 at 15:54

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 <> 2019 and (policynumber is not null) 
and (PolicyAcres <> 0) and (Policyliability <> 0 or PolicyAcres <= 0) and (Endorsement is null)

answered May 03 '19 at 15:54

Lukasz Szozda

162,964
23
234
275

I've attempted this but get the error ' Column names in each table must be unique. Column name 'CROPNAME' in table 'InitialJoinwithCPCL' is specified more than once.' – actuario99 May 03 '19 at 16:09
1

@actuario99 That's why you have to avoid * and specify column list explicitly. You have column that exists in both tables so you need to add alias or skip it at all. – Lukasz Szozda May 03 '19 at 16:10
I've specified the columns with the additional ,b.[CROPNAME] ,b.[ST] ,b.[CLASS] but get the following error 'Column names in each table must be unique. Column name 'CROPNAME' in table 'InitialJoinwithCPCL' is specified more than once.' – actuario99 May 03 '19 at 16:11
@actuario99 Please clarify via edits, not comments. Give a [mcve]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); clear specification & explanation. Edit your post to be the best presentation at the time that doesn't invalidate reasonable posted answers. – philipxy May 06 '19 at 22:02

score 0 · Answer 2 · answered May 03 '19 at 16:09

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

CREATE TABLE InitialJoinwithCPCL ( [Id] bigint, [Name] nvarchar(max), .... )
INSERT INTO InitialJoinwithCPCL
SELECT * 
FROM [dbo].[Combined] as a
LEFT JOIN [dbo].[CPCL] as b
on a.[StateAbbr] = b.[ST] and a.[CropName] = b.[CROPNAME]
WHERE cropyear <> 2019 and (policynumber is not null) 
AND (PolicyAcres <> 0) and (Policyliability <> 0 or PolicyAcres <= 0) AND 
(Endorsement is null)

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

Create new table from query results

2 Answers2

Linked