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

+3


source to share


2 answers


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

+2


source


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.

0


source







All Articles