Data Exchange Between Two Tables 1

[WayneBecker]

I want to exchange data between one table and another. Table A will ship with the new application. Table B will already reside on the customers hard drive from a previous version. The customer may have added records in Table B that do not exist in Table A. They both have the same fields. They are indexed on the same three fields.

I want to step through the two tables. For the records that are the same, based on the three index fields, I want to replace the fields in Table A with the data in five fields in Table B. If we find the customer has added a record into Table B that is not in Table A, I want to append the full record to Table A.

Table A and Table B Fields:

cBOB, nNumber, cSub, cDenomination, cComments, cMint, cUsed, cFDC, cBlock, mDescribe, etc.

Table A and Table B Index:

cBob+STR(nNumber,7,0)+cSub

Fields to be replaced in Table A from Table B when the records are the same:

cMint, cUsed, cFDC, cBlock, mDescribe

Thanks in advance for your kind help..............Wayne

 

[csr]

Sounds like you are making this much more complicated than it needs to be.

Why not just remove all data from table A and append everything from table B ?

Don
dond@csrinc.com

 

[ramani]

Hi WayneBecker,

What I understand is..
1. You will ship TableA to client having some records.
2. Client has TableB with some records.
3. Table structure, index, fields .. everything is same
4. You want to have all the records what client has and add records which he may not have from out of what you ship.
(This is same as you update TableA with all matching TableB records and add not matching TableB records to existing TableA records)

So you can create a cursor of new records you have shipped.. as Step 1 and then append the records to TableB.
SO to do this...

SELECT * FROM TableA INTO CURSOR myCursor ;
WHERE cBob+STR(nNumber,7,0)+cSub NOT IN ;
(SELECT cBob+STR(nNumber,7,0)+cSub FROM tableB)

SELECT tableB
APPEND FROM myCursor
[COLOR=/]
Hope this helps you
ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[darrellblackhawk]

If I understood your post, I think this will work:

use tableA order xyz alias new_table in 0
use tableB order xyz alias cst_table in 0

select cst_table
go top
scan
if seek(cBob+str(nNumber,7,0)+cSub,"new_table&quot
* Seems from your description that this will be the most common case,
* so place it here to avoid performing an unneeded test

* Replace field values in tableA(new_table) with field values from tableB(cst_table)

replace ;
cMint with cst_table.cMint, ;
cUsed with cst_table.cUsed, ;
cFDC with cst_table.cFDC, ;
cBlock with cst_table.cBlock, ;
mDescribe with cst_table.mDescribe in "new_table"

else && Record in tableB(cst_table) doesn't exist in tableA(new_table)

* Insert current record from tableB(cst_table) into tableA(new_table)

insert into new_table ( cBOB, nNumber, cSub, cDenomination, cComments, cMint, ;
cUsed, cFDC, cBlock, mDescribe, etc... ) values ;
( cst_table.cBOB, cst_table.nNumber, cst_table.cSub, cst_table.cDenomination, ;
cst_table.cComments, cst_table.cMint, cst_table.cUsed, cst_table.cFDC, ;
cst_table.cBlock, cst_table.mDescribe, cst_table.etc... )

endif
endscan

* Fini... I guess...

Darrell 'We all must do the hard bits so when we get bit we know where to bite'

 

[WayneBecker]

Thanks very much for the replies.

Darrell, you have grasped my problem and I will try your solution. The Table A has in the order of 5000 records and table B can have any where from 4000 to 5500 depending on how many records the customer has added or what version of the program he is working with. I have issued 11 earlier versions but all of them in Clipper. This is my first attempt with FoxPro and there have been many challenges. It is only with help like yours that I have got this far.

Thanks..................Wayne

 

[WayneBecker]

Darrell, I have not used the ORDER command in any coding so far. I have indexed the two tables on bob+str(number,7,0)+sub to an index file of the same name but I can't get past the xyz as it says these are not defined. I really have no clue where to go from here. I have filled in all the fields from the two tables, new_table and cst_table.

the first two lines of code are now:
use c:\canstamp\data\cantest order xyz alias new_table in 0
use c:\canstamp\data\can_upload order xyz alias cst_table in 0

The rest are pretty well as you wrote them above.

As you can hopefully tell I have had not much experience in Visual FoxPro.

Your continued help would be greatly appreciated.

Thanks.....................Wayne

wpbecker@sympatico.ca

 

[darrellblackhawk]

WayneBecker:

By xyz I was referring to the name of the index tag YOU would use for the tables. Since I didn't know what you would call them, I referred to them using the age-old xyz.

{For instance}
You might use the command:
index on bob+str(number,7,0)+sub tag main additive -
when creating the indexes for the tables, so, xyz would be replaced by main in the table(s)
open command(s).

Resulting in:
use c:\canstamp\data\cantest order main alias new_table in 0.

The actual index tag names are up to your discretion.


Good luck!
Darrell
'We all must do the hard bits so when we get bit we know where to bite'

 

[darrellblackhawk]

Also, read up on the use of the index command and the
differences between - .idx(independent index), .cdx(compound index), and structural index files.

Look up the index command and then do a search on .idx and .cdx in the help file.

Hope helps!

Darrell

{Some details}

independent .idx
Index files containing one index entry. An independent index file cannot have the same name as the associated table and will not automatically open with the table. See also compound index file.

compound index file
An index file containing multiple index entries, called tags. A compound index file has a .cdx file extension. You must explicitly open this index file and it must be active when you update a table. See also structural compound index.

structural compound index
An index file that is automatically opened every time its associated table is opened, and automatically updated when that table changes. A structural compound index always has the same base name as the table, but with a .cdx extension.

By using the command:
index on bob+str(number,7,0)+sub tag main additive,
you create a structural compound index.
'We all must do the hard bits so when we get bit we know where to bite'

 

[WayneBecker]

Thank-you Darrell. the program works like a charm. It brought in all the data from certain fields into the supplied database as well as adding in all the records that the customer had added.

Thanks for the detailed help that a novice at this Visual FoxPro requires.

Thanks...........................Wayne

 

[darrellblackhawk]

You're welcome.
'We all must do the hard bits so when we get bit we know where to bite'