Bulk merging data into existing table

[MikeMV]

I have 2 tables in VFP9, I need to update records from the secondary table into the main table, I have been doing this using do while loops, but I am wondering if there is a more efficient way to do this.

The main table is:

sales.stockcode, sales.yeartodate, sales.lastyear, etc. which are populated with data and it also has empty columns such as january, february, etc.

The secondary table is:

monthly.stockcode, monthly.january, monthly.february, etc.

Both tables are indexed on stockcode. I need to update the monthly records in sales with the data in monthly.

Is there a way to get this done faster?

I will greatly appreciate your feedback.

 

[Olaf Doschke]

you alredy got answers in the Thread "Consolidating 2 tables into one". If that didn't help, what's not working? Instead of starting the question all over, please continue there.

Bye, Olaf.

 

[Olaf Doschke]

okay, it's only rather similar, not the same question.

Update sales from monthly set january = monthly.january, .... where sales.stockcode = monthly.stockcode
[code]

Perhaps the monthly-table should have a year column you could match with the sales.yeartodate, or whatever should match. Otherwise you'd update all records with the same stockcode with one months data. Even if the monthly table only has data for the current year, you'd need to specify that in the where clause.

Bye, Olaf.

 

[MikeMV]

Thanks Olaf,

The sales table will only contain data for the current year, there is no danger of overwriting previous year data.

Thanks again for your help.

 

[MikeMV]

I have the suggestion implemented and it is saving me a lot of code, I need to expand on it to insert records when there are no matching records in the destination database, what I have now is:

UPDATE saleshistory FROM vconversions SET conversions = vconversions.conversions;
where (saleshistory.stockcode = vconversions.stockcode) AND (saleshistory.warehouse=vconversions.warehouse)

That works as long as there is a matching saleshistory.stockcode and saleshistory.warehouse, but is there a way to insert a new record and populate it from the vconversions if there are no matches?

I will greatly appreciate your feedback.

 

[Olaf Doschke]

For that you'll need an update and an insert, there is no way an update does insert records or an insert to update records.

So keep your update and add an insert like this:

Insert Into saleshistory;
(stockcode, conversions, warehouse);
Select;
vconversions.stockcode,;
vconversions.conversions,;
vconversions.warehouse;
From vconversions;
Left join saleshistory;
On  (saleshistory.stockcode = vconversions.stockcode);
And (saleshistory.warehouse = vconversions.warehouse);
Where saleshistory.stockcode is null

The trick is, this joins existing saleshistory records to vconversions, and then skips these and only keeps the ones, where no join is found (which is tested by saleshistory.stockcode is null), and those are then inserted.

Don't be confused, there is no real record in saleshistory.stockcode being null, but within the query the joined data is tested, and that is null if there is no matching data to join, so that's like a "where not exists" logic.

Bye, Olaf.