Updating one table from another

Posted by / 05-Sep-2020 20:03

Updating one table from another

Would you like to answer one of these unanswered questions instead?I have two tables in different databases on the same database server.Ok, this is the first time I'm trying something like this, so I don't have a frame of reference to start from.I hope I am explaining this properly: I have a source of data for equipment.In Microsoft Access 2007, I'm trying to update about 6000 records from one table with values from another table, but I'm having problems. When you have finished adding your tables, click on the Close button.Actually I am trying to update the Bot table MFG with the Big table MFG when Big. In this example, we've selected the Big and Bot tables.

where (column1,column2) in ( If your table t1 and it's backup t2 have many columns, here's a compact way to do it.Does anyone have any ideas for a better way to do this? When the m8054_table contains its anticipated 95 million plus rows, they will use 19,742 MB of storage. Here is the code: CREATE OR REPLACE PROCEDURE UPDATE_M8054_SUMMARY_FROM_TEMP IS BEGIN DECLARE /* Select information from temp_summary a row at a time */ CURSOR c_temp_summary IS SELECT ser_nbr, batch_id, fare_media_type, issue_llt, validity_period, issue_equip_id, initial_value, io_code, debit_acct_nbr, expire_llt, first_use_llt, last_use_llt, card_status, value_remain, rides_remain, xfers_remain, last_route, total_uses, total_rides, total_xfers, total_value_deduct, total_value_added, total_bonus_added, total_incent_added, total_no_entry, total_free_entry, total_free_xfer, total_test_tckt FROM temp_summary; TYPE t_cursor IS REF CURSOR; v_cursor t_cursor; v_m8054_summary m8054_summary%ROWTYPE; v_ser_nbr NUMBER(12, 0); v_batch_id NUMBER(12, 0); v_fare_media_type NUMBER(3, 0); v_issue_llt DATE; v_validity_period NUMBER(5, 0); v_issue_equip_id VARCHAR2(8); v_initial_value NUMBER(5, 0); v_io_code NUMBER(5, 0); v_debit_acct_nbr NUMBER(12, 0); v_expire_llt DATE; v_first_use_llt DATE; v_last_use_llt DATE; v_card_status NUMBER(3, 0); v_value_remain NUMBER(5, 0); v_rides_remain NUMBER(3, 0); v_xfers_remain NUMBER(3, 0); v_last_route NUMBER(5, 0); v_total_uses NUMBER(5, 0); v_total_rides NUMBER(5, 0); v_total_xfers NUMBER(5, 0); v_total_value_deduct NUMBER(12, 0); v_total_value_added NUMBER(12, 0); v_total_bonus_added NUMBER(12, 0); v_total_incent_added NUMBER(12, 0); v_total_no_entry NUMBER(5, 0); v_total_free_entry NUMBER(5, 0); v_total_free_xfer NUMBER(5, 0); v_total_test_tckt NUMBER(5, 0); v_rows_read NUMBER(12,0); v_rows_inserted NUMBER(12,0); v_rows_updated NUMBER(12,0); BEGIN DBMS_OUTPUT.ENABLE(1000000); OPEN c_temp_summary; v_rows_read := 0; v_rows_inserted := 0; v_rows_updated := 0; LOOP /* Fetch data from temp_summary into cursor */ FETCH c_temp_summary INTO v_ser_nbr, v_batch_id, v_fare_media_type, v_issue_llt, v_validity_period, v_issue_equip_id, v_initial_value, v_io_code, v_debit_acct_nbr, v_expire_llt, v_first_use_llt, v_last_use_llt, v_card_status, v_value_remain, v_rides_remain, v_xfers_remain, v_last_route, v_total_uses, v_total_rides, v_total_xfers, v_total_value_deduct, v_total_value_added, v_total_bonus_added, v_total_incent_added, v_total_no_entry, v_total_free_entry, v_total_free_xfer, v_total_test_tckt; EXIT when c_temp_summary%NOTFOUND; v_rows_read := v_rows_read 1; /* Open dynamic cursor and fetch it to check for existence of row in m8054_summary */ OPEN v_cursor FOR SELECT ser_nbr, batch_id, fare_media_type, issue_llt, validity_period, issue_equip_id, initial_value, io_code, debit_acct_nbr, expire_llt, first_use_llt, last_use_llt, card_status, value_remain, rides_remain, xfers_remain, last_route, total_uses, total_rides, total_xfers, total_value_deduct, total_value_added, total_bonus_added, total_incent_added, total_no_entry, total_free_entry, total_free_xfer, total_test_tckt FROM m8054_summary WHERE ser_nbr = v_ser_nbr; FETCH v_cursor into v_m8054_summary; /* If row does not exist in m8054_summary, insert it */ IF v_cursor%NOTFOUND THEN INSERT INTO m8054_summary VALUES( v_ser_nbr, v_batch_id, v_fare_media_type, v_issue_llt, v_validity_period, v_issue_equip_id, v_initial_value, v_io_code, v_debit_acct_nbr, v_expire_llt, v_first_use_llt, v_last_use_llt, v_card_status, v_value_remain, v_rides_remain, v_xfers_remain, v_last_route, v_total_uses, v_total_rides, v_total_xfers, v_total_value_deduct, v_total_value_added, v_total_bonus_added, v_total_incent_added, v_total_no_entry, v_total_free_entry, v_total_free_xfer, v_total_test_tckt ); v_rows_inserted := v_rows_inserted 1; ELSE /* Otherwise, update it */ UPDATE m8054_summary SET batch_id = v_batch_id, fare_media_type = v_fare_media_type, issue_llt = v_issue_llt, validity_period = v_validity_period, issue_equip_id = v_issue_equip_id, initial_value = v_initial_value, io_code = v_io_code, debit_acct_nbr = v_debit_acct_nbr, expire_llt = v_expire_llt, first_use_llt = v_first_use_llt, last_use_llt = v_last_use_llt, card_status = v_card_status, value_remain = v_value_remain, rides_remain = v_rides_remain, xfers_remain = v_xfers_remain, last_route = v_last_route, total_uses = v_total_uses, total_rides = v_total_rides, total_xfers = v_total_xfers, total_value_deduct = v_total_value_deduct, total_value_added = v_total_value_added, total_bonus_added = v_total_bonus_added, total_incent_added = v_total_incent_added, total_no_entry = v_total_no_entry, total_free_entry = v_total_free_entry, total_free_xfer = v_total_free_xfer, total_test_tckt = v_total_test_tckt; v_rows_updated := v_rows_updated 1; END IF; /* Commit every 10,000 rows */ IF MOD(v_rows_inserted, 10000) = 0 THEN COMMIT; END IF; IF MOD(v_rows_updated, 10000) = 0 THEN COMMIT; END IF; /* Report row number processed every 10,000 rows */ IF MOD(v_rows_inserted, 10000) = 0 THEN DBMS_OUTPUT.Both the databases have the same structure, but different data.Database1 (Test1) is the latest, and database2 (Test2) is an old copy of the database.

updating one table from another-6updating one table from another-12updating one table from another-24

I am using the dynamic cursor so that I can use the %NOTFOUND feature and process an insert when not found and an update when found.

One thought on “updating one table from another”

  1. Choose from hundreds of online chat rooms, Joining a online chat rooms on in is simple and fun. If you’re ready to start chat, you can join a Chat Rooms in just seconds.