如何将CTE的结果添加到SQL Server中的表中 [英] How can I get the results from a CTE to be added to a table in SQL server

问题描述

美好的一天



我怎样才能从下面的CTE中获得实际添加到表格（apicultureentries）的结果。对你的帮助表示感谢。我正在使用Sql Server 2016





使用AllMarks AS

（SELECT

竞争对手，

ApiEntryId，

AverageMark，

RANK（）超过（由AverageMark订购）AS RankPosition





来自ApicultureEntries

）

SELECT

a。*，（CASE当a.RankPosition< 14 THEN 150 - RankPosition * 10 ELSE NULL END）AS PositionEarned

来自AllMarks AS



我尝试了什么：



插入apiculture，选择进入

解决方案

这是相当的显而易见...

 使用 AllMarks  AS  
（
 ... 
）
  INSERT   INTO  TableName（< List_Of_Columns>）
  SELECT  a。< List_Of_Columns>，（ CASE   WHEN  a.RankPosition<  14  那么  150   -  RankPosition *  10   ELSE   NULL  结束） AS  PositionEarned 
  FROM  AllMarks  AS  a 

详情请见：INSERT（Transact-SQL）| Microsoft Docs [ ^ ]

Good day

how can I how can i get the results from the CTE below to be actually added to a table(apicultureentries). your help will be greatly appreciated. I am using Sql Server 2016


With AllMarks AS
(SELECT
CompetitorID,
ApiEntryId,
AverageMark,
RANK() OVER (ORDER BY AverageMark)AS RankPosition


FROM ApicultureEntries
)
SELECT
a.*,(CASE WHEN a.RankPosition < 14 THEN 150 - RankPosition * 10 ELSE NULL END) AS PositionEarned
FROM AllMarks AS a

What I have tried:

insert into apiculture , select into

解决方案

It's quite obvious...

With AllMarks AS
(
...
)
INSERT INTO TableName (<List_Of_Columns>)
SELECT a.<List_Of_Columns>, (CASE WHEN a.RankPosition < 14 THEN 150 - RankPosition * 10 ELSE NULL END) AS PositionEarned
FROM AllMarks AS a

For further details, please see: INSERT (Transact-SQL) | Microsoft Docs[^]

这篇关于如何将CTE的结果添加到SQL Server中的表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！