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

Question

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

我的查询：

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;

Answer 1

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

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条款

Answer 2

这看起来像SQL Server代码。 在该数据库中，在select子句后添加into：

Select distinct top 5 o.DocEntry, r.TrgetEntry, r.itemcode, o.DocNum as order_num, o.CardCode,
o.CardName, r.DocDate as delivery_num,
d.targettype as Ctargettype, d.trgetentry as Ctargetentry,
d.itemcode as c_itemcode, d.docentry as Cdocentry
a.CancelDate,a.Project, a.DocStatus,b.ObjType,a.ObjType
into <new table>
from ORDR o inner join
rdr1 r
On o.DocEntry = r.DocEntry left join
dln1 d
on d.TrgetEntry = r.DocEntry and
d.itemcode = r.ItemCode;

请注意，我更改了表别名，使它们有意义。 任意字母很难理解。 表缩写更有用。

我还更改了列别名，因此不需要对它们进行转义。 不要制造麻烦的别名！