Concatenate no duplicates

[#1]

Hi All,

I am trying to concatenate 2 tables - the first is a "live" table reloaded every 15 minutes and the second an archive table created weekly.

I want all values from the live table and only those rows from archive that are not duplicates. I have used:

where not exists(accountid)

but i need a where not exists values for all fields in a row as the accountid will re-occur multiple times on different dates- any ideas?

Many thanks

Mike

[Anonymous]

Mike,

Here are two options that I can think of now:

1. Concatenate all records first. After this, run LOAD DISTNCT ... RESIDENT to eliminate duplicates.

2. Create group key that includes all fields (or at least all fields which are enough for uniqueness):
autonumberhush128(field1 & field2 & ...) as Key
And then use the same not exists(Key)

(There should be more effective ways, I guess)

[#3]

Thanks, I am currently using a concatenated key to distinguish rows (which works) but it seems strange there is not a less round about and more efficient way to do this..

Mike