I have a situation where I have 2 tables
1) Orders (OrderID, OrderNo, Name, Invoice, OrderStatus) -- OrderID is an Identity Column
2) OrderDetails (DetailID, OrderID,ItemName, ItemDesc, Weight, Volume, Quantity,ItemStatus) -- DetailID is an Identity Column
Orders table will have all the Orders and OrderDetails table will have all the details for the Order.
My Source table will have all the Orders and the order details. Now I have to
Insert/Update Orders and OrderDetails into both the tables.
I am able to Insert\Update Orders and this is fine.
Problem: For Order Details,
1)I have to find wether the OrderID Exists in OrderDetails Table, If it dosent exist I have to Insert the Order and its details into the OrderDetails table and set the Detail Status to "New"
2) If the Order Exists in the OrderDetails Table, I have to check what detail attributes have changed (An Item Might have deleted or Item Code Chenges or Weight, Volume etc may Change).
a) If there is no change in the Order Attributes, There is no need to change any thing on the OrderDetails Table.
b) If there is a change on the Details for the order Coming in from the source like (An Item Might have deleted or Item Code Chenges or Weight, Volume etc may Change) then I have to Delete all the Existing records from the OrderDetails table for that OrderID and then Insert the OrderDetails for that OrderID into the OrderDetails table with ItemStatus "UP" for all the Details for that Order.
I have a problem with this 2(b) part. Please guide me with this... Thanks In Advance