Query and Update/Insert Large Tables Frequently ...

Hi All,

I have the following 2 tables:

Table 1 - Pricing:

This table holds pricing details of all Items (roughly 150,000 items). One Item has three types of prices Standard, Promotion, and Discounted. Therefore the table contains roughly 150,000 * 3 records. Also this table gets updated frequently every day.

Item# Type FromDate ToDate Price

AAA	Standard	          01/01/05	31/12/05	  1.50
AAA	Promotion          02/12/05	16/12/05	  1.40
AAA	Discounted        10/12/05	15/12/05	  1.20
BBB	Standard	           01/01/05	31/12/05	  1.60
BBB	Promotion          02/12/05	24/12/05	  1.30
BBB	Discounted        10/12/05	15/12/05	  1.50

Table 2 - BestPrice:

This table contains best price (lowest price) of all Items for previous 21 days including the current date. The POS system is accessing this table to get the daily best price for billing customers.

Item# Date BestPrice

AAA	17/12/05	    1.50
AAA	16/12/05	    1.40
AAA	15/12/05	    1.20
AAA	14/12/05	    1.20
AAA	13/12/05	    1.20
....	... . 	    ....


....	....	    ....

BBB	17/12/05	    1.30
BBB	16/12/05	    1.30
BBB	15/12/05	    1.30
BBB	14/12/05	    1.30
BBB	13/12/05	    1.30


....	....	    ....

....	....	    ....

Problem Statement:

Table 2 (BestPrice) needs to get updated from Table 1 (Pricing) couple of times every day with the best price for each Item for the current day. Also currently I don't have any data in Table 2 (BestPrice), therefore I have to first update this table to have best prices for last 20 for all Items too.

What's the most efficient method to perform this job? Received on Fri Dec 16 2005 - 17:44:55 CST