hi all,
I have a master table from which I need to copy records into processing table based on the item_code and Item_type.for each item_code there can be multiple item_type.
Master Table(MT)
Key column
mmyy_KEY
Item_code
Item_type
Processing table (PT)
key column
mmyy_kEY
Item_code
Item_type
The processing table is pre-populated with some data but my requirement is to copy the missing item_code and item_type from the master table. If the PT already has the Item_code then the missing item_type needs to be copied for the item_code.
I am writing the below query :
indent preformatted text by 4 spaces
insert into processing table
Key column
Item_code
Item_type
....other misc columns
SELECT
Key column
Item_code
Item_type
....other misc columns
FROM MASTER TABLE
WHERE mmyy_KEY = 1408
AND ITEM_CODE NOT IN
(
SELECT DISTINCT(ITEM_CODE) FROM PT
where mmyy_KEY = 1408
)
union
insert into processing table
Key column
Item_code
Item_type
....other misc columns
SELECT
Key column
Item_code
Item_type
....other misc columns
FROM MASTER TABLE
WHERE mmyy_KEY = 1408
and item_code in
(
SELECT DISTINCT(ITEM_CODE) FROM PT
where mmyy_KEY = 1408
)
indent preformatted text by 4 spaces
T he approx number of records for each item_code and item_type is around 20000K . so if there are 5 unique item_code then around 100000 records would be copied based on the above query. This process would be run on adhoc basis (around 3-4 times in a month)
Please suggest if the query is fine or can be tuned.
Thanks in Advance!!