根据查询结果创建新表 [英] 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 create table.

这是我的原始代码，我试图从此代码的输出中创建一个名为'InitialJoinwithCPCL'的新表

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));

推荐答案

SQL Server 2014不支持

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)

这篇关于根据查询结果创建新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！