时间戳

首页 > 解决方案 > 将查询结果放入 SQL Server 中的新表中

sql - 将查询结果放入 SQL Server 中的新表中

问题描述

我想将查询结果插入到新表中，有什么方法可以更改代码以便将其存储在表中。

我的查询：

SELECT DISTINCT TOP 5 a.DocEntry
    ,b.TrgetEntry
    ,b.itemcode
    ,a.DocNum AS 'Order No.'
    ,a.CardCode
    ,a.CardName
    ,b.DocDate AS [Delivery No.]
    ,c.targettype AS 'Ctargettype'
    ,c.trgetentry AS 'Ctargetentry'
    ,c.itemcode AS 'c-itemcode'
    ,c.docentry AS 'Cdocentry' a.CancelDate
    ,a.Project
    ,a.DocStatus
    ,b.ObjType
    ,a.ObjType
FROM ORDR a
INNER JOIN rdr1 b ON a.DocEntry = b.DocEntry
LEFT JOIN dln1 c ON c.TrgetEntry = b.DocEntry
    AND b.itemcode = c.ItemCode order by c.itemcode;

标签： sql

解决方案

您可以这样做，因为它将创建一个新表并将记录插入该表中。如果您已经创建了表，那么您也可以为插入和选择指定名称和各个列。

SELECT *
INTO YourTableName
FROM (
    SELECT DISTINCT TOP 5 a.DocEntry
        ,b.TrgetEntry
        ,b.itemcode
        ,a.DocNum AS 'Order No.'
        ,a.CardCode
        ,a.CardName
        ,b.DocDate AS [Delivery No.]
        ,c.targettype AS 'Ctargettype'
        ,c.trgetentry AS 'Ctargetentry'
        ,c.itemcode AS 'c-itemcode'
        ,c.docentry AS 'Cdocentry' a.CancelDate
        ,a.Project
        ,a.DocStatus
        ,b.ObjType
        ,a.ObjType
    FROM ORDR a
    INNER JOIN rdr1 b ON a.DocEntry = b.DocEntry
    LEFT JOIN dln1 c ON c.TrgetEntry = b.DocEntry
        AND b.itemcode = c.ItemCode
    )

a

对于使用 order by 子句，您可以尝试这样的事情。

SELECT DISTINCT   
        Insured_Customers.FirstName, Insured_Customers.LastName,   
        Insured_Customers.YearlyIncome, Insured_Customers.MaritalStatus  
INTO Fast_Customers from Insured_Customers INNER JOIN   
(  
        SELECT * FROM CarSensor_Data where Speed > 35   
) AS SensorD  
ON Insured_Customers.CustomerKey = SensorD.CustomerKey  
ORDER BY YearlyIncome;

您可以在此处详细了解INTO 条款

推荐阅读