SQL & PL/SQL

Hi all,

I'm trying to create an insert statement that loops through a table that has 10 years of data (2001 to 2010) month by month to minimize impact on server and commits more frequently to avoid filling up the redo logs and rollback tablespaces. The table is large, has about 40 millions records per year. Lets say the structure of the table is the following:

Customer_ID number(9),
Order_Item_1 number(6),
Order_Item_2 number(6),
Order_Item_3 number(6),
Order_date date

The table is in flat format but I want to normalize it so that it looks like the following:

Customer_ID Order_Seq Order_Item Order_date
999999999 1 555555 01-jan-2001
999999999 2 666666 01-jan-2001
999999999 3 444444 01-jan-2001
888888888 1 555555 03-jan-2001
888888888 2 666666 03-jan-2001

But because I want to loop through month by month....I need to set it up so that it loops through month by month, year by year (Using the Order Date Field) and Order_item by Order_item. Something like:

so my insert statements would be something like if I hardcoded instead of put the insert statement into a loop:


insert into orders_normalized
(Customer_id,Order_seq,Order_item,Order_date) select customer_id,1,Order_item,Order_date where Order_item_1 is not null and to_char(order_date,'yyyy') = '2001' and to_char(order_date,'mm')='01';

insert into orders_normalized
(Customer_id,Order_seq,Order_item,Order_date) select customer_id,2,Order_item,Order_date where Order_item_2 is not null and to_char(order_date,'yyyy') = '2001' and to_char(order_date,'mm')='01';

insert into orders_normalized
(Customer_id,Order_seq,Order_item,Order_date) select customer_id,3,Order_item,Order_date where Order_item_3 is not null and to_char(order_date,'yyyy') = '2001' and to_char(order_date,'mm')='01';

insert into orders_normalized
(Customer_id,Order_seq,Order_item,Order_date) select customer_id,1,Order_item,Order_date where Order_item_1 is not null and to_char(order_date,'yyyy') = '2001' and to_char(order_date,'mm')='02';

insert into orders_normalized
(Customer_id,Order_seq,Order_item,Order_date) select customer_id,2,Order_item,Order_date where Order_item_2 is not null and to_char(order_date,'yyyy') = '2001' and to_char(order_date,'mm')='02';

insert into orders_normalized
(Customer_id,Order_seq,Order_item,Order_date) select customer_id,3,Order_item,Order_date where Order_item_3 is not null and to_char(order_date,'yyyy') = '2001' and to_char(order_date,'mm')='03';

Hope this makes sense.

Thanks