StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I'm not sure why you would add a trigger. Will you be continuing to use the LEGACY_TABLE_SQL? If not then how about this one time procedure? It uses Oracle syntax but can be adapted to most databases</p> <p>PROCEDURE MIGRATE IS</p> <p>CURSOR all_data is<br> SELECT invoice_no, cust_no,Item_1_no,Item_1_qty........<br> FROM LEGACY_TABLE_SQL; </p> <p>BEGIN</p> <p>FOR data in all_data LOOP INSERT INTO INVOICE (invoice_no, cust_no) VALUES (data.invoice_no, data.cust_no); IF Item_1_no IS NOT NULL THEN INSERT INTO INVOICE_LINE_ITEM(invoice_no,Item_1_no,Item_1_qty....) VALUES(data.invoice_no,data.Item_1_no,data.Item_1_qty....) END IF; --further inserts for each item</p> <p>END LOOP;<br> COMMIT;<br> END;</p> <p>This can be further optimized in Oracle with a <a href="http://www.dba-oracle.com/t_oracle_bulk_collect.htm" rel="nofollow">BULK_COLLECT</a>. I would create the INVOICE_LINE_ITEM table with default values of 0 for all items.</p> <p>I would also consider these possibilities: is the invoice number really unique now and in the future? it may be a good idea to add a pseudo key based off a sequence<br> is there any importance to null item_no entries? Could this indicate a back order, short shipment or just bad data entry?</p> <p>EDIT: as you advise that you will continue to use the legacy table you need to prioritize what you want. Is efficiency and performance your number one priority, maintainability, synchronous transaction For example:<br> - if performance is not really critical then implement this as you outlined - if this will have to be maintained then you might want to invest more into the coding - if you do not require a synchronous transaction then you could add a column to your LEGACY_TABLE_SQL called processed with a default value of 0. Then, once a day or hour, schedule a job to get all the orders that have not been processed.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload