append unique

[DanNorris2000]

Anyone aware of how to append only unique records to a file?

 

[ramani]

Hi

INSERT INTO myTable (mykeyField) VALUE (myUniqueValue)




ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[DanNorris2000]

is that assuming only 1 key field? I want unique rows. These are free tables

 

[jimstarr]

One way would be to append all the records, then remove duplicates with

SELECT DISTINCT * FROM mytable INTO .......


Jim

 

[ramani]

HI

If you have multipe fields in a row..

INSERT INTO myTable (mykeyField, field2, field3, ...) ;
VALUE (myUniqueValue, var2, var3, .. )

Insert will be much more faster than append an then replace also.

You need to have the values first unique and properly checked as unique before the insert command is done.

IF variables . check unique..
INSERT INTO myTable (mykeyField, field2, field3, ...) ;
VALUE (myUniqueValue, var2, var3, .. )
ENDIF




ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[DanNorris2000]

IF variables . check unique..
INSERT INTO tmprilossum();
VALUE (reingrp, bfrfter, cpysts, fullname)
ENDIF

where would the from filename go?

 

[cjulio]

i don't no what you whant to do but why don't you make a sql joining all of the free tables fieltering the records into another tmp table and use append from table...

sory if this is not what you whant...

 

[Stella740pl]

DanNorris2000,
Do you want to append from one table to another?
Try this:

SELECT DISTINCT * FROM Source_tbl INTO CURSOR tmp_recs
SELECT Target_tbl
APPEND FROM DBF('tmp_recs')

Stella

 

[DanNorris2000]

I want to append from one table to another but I only want unique records to be appended where key is (reingrp, bfrfter, cpysts, fullname)

 

[cjulio]

whi don't you use group by reingrp, bfrfter, cpysts, fullname in the sql....

 

[Stella740pl]

Well, then the above should work - in general.
For more detail on this I would probably need to know structure of both tables. Do you want to append only these 4 fields, or to use only these fields for selecting unique records, and does your source table have more fields than you mentioned?

 

[DanNorris2000]

I dont want to append only those 4 fields but base the uniqueness on that key. The table do have more fields than noted

 

[jimstarr]

SELECT table1
INDEX ON reingrp + bfrfter + cpysts + fullname TAG keyx
SET ORDER TO keyx

SELECT table2
SCAN FOR NOT SEEK(reingrp + bfrfter + cpysts + fullname, 'table1')
INSERT INTO table1 VALUES(table2.fld1, table2.fld2, etc.)
ENDSCAN


Jim

 

[DanNorris2000]

Thats it, thanks

 

[Stella740pl]

Then you can add cjulio's grouping suggestion to this.
Say, like this:

SELECT * ;
FROM Source_tbl ;
GROUP BY reingrp, bfrfter, cpysts, fullname ;
INTO CURSOR tmp_recs
SELECT Target_tbl
APPEND FROM DBF('tmp_recs')

This will select you only one record for each combination of these fields.
Stella

 

[Stella740pl]

Well, if you mean that you need to check if the records don't already exist in the target table, then you can do the following:

SELECT * ;
FROM Source_tbl ;
WHERE reingrp+bfrfter+cpysts+fullname NOT IN ;
(SELECT reingrp+bfrfter+cpysts+fullname ;
FROM Target_tbl) ;
GROUP BY reingrp, bfrfter, cpysts, fullname ;
INTO CURSOR tmp_recs

SELECT Target_tbl
APPEND FROM DBF('tmp_recs')