我有一个有10列的表格。我必须为该表插入数据。我有一些插入语句。 first insert语句从一个源插入前3行的数据。现在,我想使用来自不同来源的另一个插入语句为同一行中的下一列插入数据。这些插入查询每天针对表Order_Warehouse_Status运行,因此我们将有1行用于日常交易。
例如表Order_Warehouse_Status有10列,例如
Printed_PPS_Shipment,
Printed_Shipment_Lines,
Printed_Unit,
Picking_Scheduled_Orders,
Picking_Scheduled_Lines,
Picking_Scheduled_Units,
Pick_Complete_Orders,
Pick_Complete_Lines,
Pick_Complete_Units
在第一个查询下方,将数据插入前三列。第二个查询应在同一行中为下一列插入数据。如何实现呢?
-第一查询
insert into Order_Warehouse_Status
(date , Printed_PPS_Shipment,
Printed_Shipment_Lines,
Printed_Unit)
SELECT Getdate(), count(v_c_ship_ship_id) as Printed_PPS_Shipment,
count(ship_l_id) as Printed_Shipment_Lines,
count(allocated_qty) as Printed_Unit \
FROM [STG_WMS_Status_PPS_Line_QTY]
where CONVERT(DATE,Inserted_date )=CONVERT(DATE,Getdate())
and shipment_status=2
-第二查询
insert into Order_Warehouse_Status
(Date, Picking_Scheduled_Orders,
Picking_Scheduled_Lines,
Picking_Scheduled_Units)
SELECT Getdate(), count(v_c_ship_ship_id) as Picking_Scheduled_Orders,
count(ship_l_id) as Picking_Scheduled_Lines,
count(allocated_qty) as Picking_Scheduled_Units
FROM STG_Closed_Received
where CONVERT(DATE,Inserted_date )=CONVERT(DATE,Getdate())
and shipment_status=7
提前致谢
最佳答案
嗨,第一个查询与插入查询相同,第二个查询可以通过检查日期是否为今天的日期来执行带where条件的Update语句。
Update Order_Warehouse_Status
set Picking_scheduled_orders = i.Picking_Scheduled_Orders,
Picking_Scheduled_Lines = i.Picking_Scheduled_Lines,
Picking_Scheduled_Units = i.Picking_Scheduled_Units
From(SELECT count(v_c_ship_ship_id) as Picking_Scheduled_Orders,
count(ship_l_id) as Picking_Scheduled_Lines,
count(allocated_qty) as Picking_Scheduled_Units
FROM STG_Closed_Received
where CONVERT(DATE,Inserted_date )=CONVERT(DATE,Getdate())
and shipment_status=7)i
Where CONVERT (Date,'date column of Order_Warehouse_Status)
= CONVERT(DATE,Getdate())
不需要更新'date'列,因为它已经被插入到第一个查询中。如果内部select语句仅返回一行,希望此方法有效。
关于sql - 在sql中对现有行进行多次插入查询,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/30181427/