I've tried SqlLines. It misses a lot more often than it hits. It's mostly worthless. I've had much better success with Microsoft's SQL Server Migration Assistant. It's not perfect but it does a pretty good job... and it's FREE!

The temporary table is just for show. Eventually that will be replaced with actual database tables. So for the three temporary tables, ignore that for now and treat them as actual oracle tables. However, I need to query the first table "EXCEL_TEMP" and put into a variable to loop.

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

So this is my three tables. The table CUSTOMER_ORDER and CUST_ORDER_LINE are standard tables and they do have a lot more fields, but the fields that I am working with are in the diagram below. The table EXCEL_TEMP will be another table that we will importing data from an excel file into that table using a windows tool we create so that user can select the excel file they want to import data into that table.

The final output would be in the CUST_ORDER_LINE. If the line does not exists, create a new line in the CUST_ORDER_LINE table and auto increment the line number. If a record exists in CUST_ORDER_LINE table, then update the QTY field with the labor hours from the EXCEL_TEMP table.

Final result would be as the below diagram. Thus I had created 3 temporary table to illustrate.

I slightly modified the ROW_NUMBER from what you provided in the original post because I didn't understand the need for all the "+ X.LINE_NO) - X.LINE_NO" stuff.

Feel free to change it back if you want.

merge into cust_order_line colusing( select order_id, row_number() over(partition by customer_id order by order_id,service_id) line_no, service_id, sum(hours) hours from excel_temp group by customer_id,order_id,service_id,task) eton (col.order_id=et.order_id and col.line=et.line_no and col.service_id=et.service_id)when matched then update set col.qty=et.hourswhen not matched then insert(col.order_id,col.line,col.service_id,col.qty) values(et.order_id,et.line_no,et.service_id,et.hours)/

The row_number(), it is looking at the cust_order_line for the max line_no and then append from there. So as you can see in the example, the order_id 0960 already have 2 lines in the cust_order_line table. It then take that and increment it by 1 making it line_no 3.

I am not familiar with PL/SQL since that got dumped on me with a site that is on oracle and we currently use MS SQL. So just a quick question, does the query you provide look at the max line_no and then increment from there?

Sorry I didn't get a chance to review this yesterday, but will review today and post back. I do have a question though since this is just straight SQL, I can use this on a Oracle database without issue correct?

Also another question with the sql query, I also need to check to ensure that the sales order also exists before I do any update or insert "CUSTOMER_ORDER".

I'm not familiar with the "MERGE INTO", how can I also incorporate that? If you see my original query, I checked "IF EXISTS(SELECT 1 FROM @CUSTOMER_ORDER". The "CUSTOMER_ORDER" is the header and that need to exists before I start update/inserting the line detail to the sales order. Otherwise, it would skip updating/inserting for that sales order.

Also I know from what you posted, it can be just a straight query from my understanding with your example. How hard is it to translate the above to use with PL/SQL cursor if I want to do more? Example, inside the loop, I want to total the total_amt for all the lines and add that to the "CUSTOMER_ORDER" since there's a total_amt field in there that takes the total calculation of all the line detail amt. There are a few other tables I also need to update inside of that loop as well.

Here is the MERGE that joins to the customer_order table to ensure the order exists:

merge into cust_order_line colusing( select order_id, row_number() over(partition by et.customer_id order by order_id,service_id) line_no, service_id, sum(hours) hours from excel_temp et join customer_order co on et.customer_id=co.customer_id and et.order_id=co.id group by et.customer_id,order_id,service_id,task) eton (col.cust_order_id=et.order_id and col.line_no=et.line_no and col.service_charge_id=et.service_id)when matched then update set col.qty=et.hourswhen not matched then insert(col.cust_order_id,col.line_no,col.service_charge_id,col.qty) values(et.order_id,et.line_no,et.service_id,et.hours)/

I assumed a primary key on cust_order_line of: cust_order_id,line_no,service_charge_id.

The primary key makes things easier so I can leverage the exception handler and don't have to keep querying the tables over and over again.

begin for i in ( select order_id, row_number() over(partition by et.customer_id order by order_id,service_id) line_no, service_id, sum(hours) hours from excel_temp et join customer_order co on et.customer_id=co.customer_id and et.order_id=co.id group by et.customer_id,order_id,service_id,task ) loop begin insert into cust_order_line(cust_order_id,line_no,service_charge_id,qty) values(i.order_id, i.line_no, i.service_id, i.hours); exception when dup_val_on_index then update cust_order_line set qty=i.hours where cust_order_id=i.order_id and line_no=i.line_no and service_charge_id=i.service_id; end; end loop;end;/

I tried both the query and was able to get the PL/SQL with the loop to work. Only question is that when it's doing either the update/insert, how can I have it check the cust_order_line table first before determining if it's an update or insert?

Before making the actual insert/update, I want to output to text to ensure I am updating correctly. In my test, I created a sales order with 1 line in the cust_order_line table. In my excel_temp table, I have 2 records with one record same as line 1. It should update the first record since it match what's already in line 1 in the cust_order_line, but record 2 should be an insert. However, it is not seeing what's already in the cust_order_line table. What would I need to add so it check the "CUST_ORDER_LINE" table for existing detail records? I am checking the line for "ORDER_ID" and "SERVICE_ID". If both exists, then update else insert.

As for the "Merge Into" example, can I do similar as well by doing the output to test before an actual table insert/update?

I did a bit of search and seems I can use the following:

merge into cust_order_line colusing( select order_id, row_number() over(partition by et.customer_id order by order_id,service_id) line_no, service_id, sum(hours) hours from excel_temp et join customer_order co on et.customer_id=co.customer_id and et.order_id=co.id group by et.customer_id,order_id,service_id,task) eton (col.cust_order_id=et.order_id and col.line_no=et.line_no and col.service_charge_id=et.service_id)when matched then update set col.qty=et.hourswhen not matched then insert(col.cust_order_id,col.line_no,col.service_charge_id,col.qty) values(et.order_id,et.line_no,et.service_id,et.hours)OUTPUT $action, update.*, inserted.*

Thank you for the clarification. Also do I need to add commit for both script you provided since this is for oracle?

Example

when matched then update set col.qty=et.hours commit;when not matched then insert(col.cust_order_id,col.line_no,col.service_charge_id,col.qty) values(et.order_id,et.line_no,et.service_id,et.hours) commit;

I'm not completely following the new requirement but multiplying two values can probably be done in the select statement itself. If for some reason it cannot be, then yes, you can create variables and perform the math inside the loop itself.