Consolidate 2 tables into one

[MikeMV]

Hello,

I have 2 tables that I need to consolidate into one, I usually do this using a do while loop, but I am wondering if there is an easier way.

All 3 tables have the following structure:

stockcode, january, february, march, etc. I have to sum each pair of months into a single row for each stockcode.

The 3 are local dbfs and are:

sales1 (source1)
sales2 (source2)
sales (destination)

I will greatly appreciate any suggestion on how to simplify this proccess.

 

[bborissov]

If you use VFP9:

INSERT INTO Sales (field list here);
SELECT StockCode,;
       SUM(JAN) AS Jan,;
       ... 
       SUM(Dec) AS Dec;
FROM (SELECT * FROM Sales1;
      UNION ALL;
      SELECT * FROM Sales2) Tbl1;
GROUP BY StockCode

NOT TESTED!

For previous versions you should split this in 3 selects:

SELECT * FROM Sales1;
UNION ALL;
SELECT * FROM Sales2;
INTO CURSOR crsTest

SELECT StockCode,
       SUM(JAN) AS Jan,;
       ... 
       SUM(Dec) AS Dec;
FROM crsTest;
GROUP BY StockCode;
INTO CURSOR Tbl1

SELECT Sales
APPEND FROM (DBF("Tbl"))

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[MikeMV]

Thanks for helping me optimize this process Borislav