Multiple Insert query for existing row in sql
I have a table with 10 columns. I need to insert data for this table. I have multiple insert statements. the first insert statement inserts data for the first 3 rows from the same source. Now I want to insert data for the following columns in one row using other inserts from different sources. These insert queries are run daily for the Order_Warehouse_Status table, so we will have 1 row for a daily transaction.
Ex. The Order_Warehouse_Status table has 10 columns, for example
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
Below the first query, insert data in the first 3 columns. The second query should insert data for the next column in the same row. How do you achieve this?
- 1st request
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
- Second request
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
Thank you in advance
source to share
Hello the first query will be the same as insert and the second query you can do with Update statement with where clause checking if date is today's date ...
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())
There is no need to update the "date" column because it is already inserted in the first query. Hope this works if the inner select statement only returns one row. Just check
source to share
Both of your first and second queries only return one row - some summary for the current date.
It would be logical to calculate this summary in one query and the INSERT
full result as one row instead of inserting the partial result first and then looking for the row that was inserted to update the found row with the second half of the result.
WITH
CTE_Printed
AS
(
SELECT
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
)
,CTE_Picking_Scheduled
AS
(
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
)
INSERT INTO Order_Warehouse_Status
([Date],
Printed_PPS_Shipment,
Printed_Shipment_Lines,
Printed_Unit,
Picking_Scheduled_Orders,
Picking_Scheduled_Lines,
Picking_Scheduled_Units)
SELECT
GETDATE() AS [Date],
Printed_PPS_Shipment,
Printed_Shipment_Lines,
Printed_Unit,
Picking_Scheduled_Orders,
Picking_Scheduled_Lines,
Picking_Scheduled_Units
FROM
CTE_Printed CROSS JOIN CTE_Picking_Scheduled
;
Note that I am concatenating the results of the two queries using CROSS JOIN
here with no strings attached. It will work efficiently and correctly in this case, because each of the two queries returns exactly one row.
If you come across a situation where you end up with multiple lines, then you will need to define a method to combine the two results using some condition.
source to share