[mythtv-users] Possibly OT: Merging Tables, NO duplicates.
listbox at swaffordfamily.com
Tue Nov 11 02:16:21 UTC 2008
Maybe write a little PHP/Perl script that pulls from SQLlite and inserts
into mysql with the keys updated. Or export to a file and read/insert with
PHP/Perl. Run script, change connector on machine to start updating mysql.
Move to next machine.
It is probably a sacrilege, but if it where me, I would suck those tables
from all 3 machines into Excel using it's ODBC import function (or export
and paste into Excel if necessary), then massage the keys and use Excel's
"concatenate" function to spit out SQL insert statements. If you have
multiple tables and keys between them, the "vlookup" function is a great one
too. Not sure if OpenOffice.org has that functionality but I am setting up
a PC in my spare time to try and replace all I do in Excel so I'll find out
someday. Right now I couldn't make a living without Excel.
I also might think about adding a new columns for the machine name and
existing index/key. Like "machinea,1", "machinea,2", "machineb,1",
"machineb,2", .... if you care to keep that history.
>From: mythtv-users-bounces at mythtv.org
>[mailto:mythtv-users-bounces at mythtv.org] On Behalf Of R. G. Newbury
>Sent: Monday, November 10, 2008 4:31 PM
>Subject: [mythtv-users] Possibly OT: Merging Tables, NO duplicates.
> I am having a problem which I am sure has been addressed by someone
>before now, and I don't wish to waste time re-inventing the wheel,
>I have a small (presently command-line controlled) sqlite program to
>keep track of time usage. It runs on 3 different physical computers. I
>need to merge the entries, *without duplication*. All three units run
>the same app and the tables are the same. But I want the
>entries I make
>on the laptop and/or the Nokia tablet to end up merged into
>on the desktop.
>At the moment I cannot see how I can select 'distinct' from 2
>I *think* to do this, I will need to copy each table onto the 'master
>presuming each row on each copy of the table to have a unique primary
>key based on the entry values;
>and at 'merge time',
>b) update seriatim from all the various instances into a necessarily
>large temporary table number 1; then,
>c) update into temporary table 2, using a 'select distinct' to obtain
>the unique entries; and finally,
>d) copy the resulting temporary table 2, back over the orginal
>So if table A has rows 'a,b.c' and table B has 'a,b,c,d' and
>table C has
>'a,b,c,e', temp 1 will have 'a,a,a,b,b,b,c,c,c,d,e' and table
>2 will end
>up with 'a,b,c,d,e'...and will be copied over A, B and C.
>Is this the way to do it, or am I missing something really
>easy, such as
>a horribly complex 'select....join' statement. Note that the schema of
>all of these tables is exactly the same.
>(And the answer to this has some relevance to updating/merging
>instances of mythtv tables....such as moving a mythfilldatabase update
>from one machine to another without replacing the entire table.)
> Please let me know if anything I say offends you.
> I may wish to offend you again in the future.
> Tux says: "Be regular. Eat cron flakes."
>mythtv-users mailing list
>mythtv-users at mythtv.org
More information about the mythtv-users