How to merge two tables

[Saif_Abc]

Hi,

I have one history file (Account.dbf) containing records from 1.1.2010 to 31.12.2015, and I have one current file (Actran.dbf) which contains records from 1.1.2016 till today.

How to combine these two tables if a user wants to fetch data from 1.1.2014 to 31.5.2022.

Structure of Both History and Current tables are as follows:
-----------------------------------------------------------
1. cReference c(8)
2. cDate d
3. Debit n(13,2)
4. Credit n(13,2)
5. Remarks

Thanks

Saif

 

[Chris Miller]

See Union:

Select * From Account;
Union All;
Select * From Actran;
Where cDate>={^2014-01-01};
Into Cursor Result

In such a case the structure is of no interest, it just has to be the same for both tables. You'll see when this is not the case, as it'll error. The other important restriction is that the result has less than 2GB. I can't tell you if that's the case when you want to cover 2014 to today. Also you'll see whether you err about cDate being a D field. I trust you, but the inconsequential field naming makes me cautious.

It should be natural you only need a small subset of data as a result, but for sake of making it explicitly clear, a union without any where condition would join all data just like append would do that into a result workarea and that will cause an error when reaching 2GB. If that was the reason for the split, then it'll clearly not work. So what you cannot do is use a SQL-Select without Where and then SET FILTER.

If you want that and therefore create one new table with all data since 2014 then save that union result with COPY TO, for example, or make the query INTO TABLE, or create a third empty dbf file and then append with FOR condition of the old table.

Chriss

 

[Saif_Abc]

Thanks a bunch!!