Re: merge multiple databases

From: DIOS (sindizzy.pak_at_softhome.net)
Date: 05/11/04 

Date: Tue, 11 May 2004 17:03:48 GMT

That is a very interesting algoithm. It might work so ill give it a shot.
I will create the tempID fiields and then run my queries and updates against
those
and just inputa temp number for tha actual Ids until i fill them in. I have
the iteration
algorithm already written but this eems like it may be more efficient and
faster.
I will post my comparisons.

AGP 

-- 
**********************************************************************
Unpak my email address before sending me personal email
**********************************************************************
"YYZ" <notapplicable> wrote in message
news:ukjCC$1NEHA.1420@TK2MSFTNGP09.phx.gbl...
> "DIOS" <sindizzy.pak@softhome.net> wrote in message
> news:snYnc.24150$xw3.1632381@attbi_s04...
> > Hmmm, I can create queries in Access or on the fly but I dont think
there
> > is a concise way of creating a query that will merge my databases into
> > one big database. Each table has a primary index which is tied in with
the
> > other
> > tables. Each database can possibly have the same primary keys. So when
> > i merge the databases i have to keep track of what the new keys are and
then
> > transfer those to the the other tables and then merge those into the new
> > database.
> > Sounds easy but I just dont see how to do it with a standard update
query.
>
> I dont know if you saw my other post, but see if this would work for you:
>
> Add 2 columns to tblDrive -- dbId and oldDriveID.
> Add 2 columns to tblFiles -- dbId and oldFileID.
> Add 1 column to tblSubFiles -- dbId
>
> dbId would be text.  You'd update every record in every table, set dbId to
some
> unique identifier for each of your old databases.  Would machine name
work?  How
> about Pathname?  You could even use a numbering scheme (NOT autonumber!
You are
> seeing why autonumber sucks sometimes, and GUIDs may work better for you
in the
> long run).
>
> After you do that for each client datbase, then you can issue 3 sql
statements
> per database, one per table,  like I showed you in my other post INSERTing
all
> records into the new datbase from teh old datbase, letting the new datbase
> assign all brand new autonumber values in DriveId (but you have
oldDriveID,
> remember?).
>
> Now you ahve all the information you need, and I think you'll just need to
issue
> 3 update statements -- here are 2 of them...
>
> UPDATE tblSubFiles INNER JOIN tblFiles ON (tblSubFiles.FileID =
> tblFiles.oldFileId AND tblSubFiles.dbId = tblFiles.dbId) SET
tblSubFiles.FileID
> = tblFiles.FileId
>
> UPDATE tblSubFiles INNER JOIN tblDrive ON (tblSubFiles.DriveId =
> tblDrive.oldDriveID AND tblSubFiles.dbId = tblDrive.dbId) SET
> tblSubFiles.DriveId = tblDrive.DriveID
>
> ' do the same thing for tblFiles
>
> Does that work for you?
>
> Matt
>
>

Relevant Pages

  • Re: merge multiple databases
    ... Each database can possibly have the same primary keys. ... > Add 2 columns to tblDrive -- dbId and oldDriveID. ... > After you do that for each client datbase, then you can issue 3 sql statements> per database, one per table, like I showed you in my other post INSERTing all> records into the new datbase from teh old datbase, letting the new datbase ...
    (microsoft.public.vb.general.discussion)
  • Re: merge multiple databases
    ... Each database can possibly have the same primary keys. ... > Add 2 columns to tblDrive -- dbId and oldDriveID. ... > After you do that for each client datbase, then you can issue 3 sql statements> per database, one per table, like I showed you in my other post INSERTing all> records into the new datbase from teh old datbase, letting the new datbase ...
    (microsoft.public.access.queries)
  • Re: merge multiple databases
    ... Each database can possibly have the same primary keys. ... > Sounds easy but I just dont see how to do it with a standard update query. ... Add 2 columns to tblDrive -- dbId and oldDriveID. ... After you do that for each client datbase, then you can issue 3 sql statements ...
    (microsoft.public.vb.general.discussion)
  • Re: merge multiple databases
    ... Each database can possibly have the same primary keys. ... > Sounds easy but I just dont see how to do it with a standard update query. ... Add 2 columns to tblDrive -- dbId and oldDriveID. ... After you do that for each client datbase, then you can issue 3 sql statements ...
    (microsoft.public.access.queries)
  • Re: merge multiple databases
    ... Each database can possibly have the same primary keys. ... > Sounds easy but I just dont see how to do it with a standard update query. ... Add 2 columns to tblDrive -- dbId and oldDriveID. ... After you do that for each client datbase, then you can issue 3 sql statements ...
    (microsoft.public.vb.database.dao)
Loading