If you are merging table A and table B and say, table A's auto-increment id is up to 2000, just pick a nice round number like 3000 and add it to the auto-increment ID column of table B with something like this:
UPDATE tableB SET id = id + 3000; Then do the same to all the fields in other tables that link to tableB's auto-increment ID column. Once that's done, merge the tables with something like: INSERT INTO tableA SELECT * FROM tableB; And do the same with the other tables (if they have their own autoincrement ids then you should leave that out of the insert into select from (unless those auto-increment ids are referenced by other tables in which case you'll have to do the same thing cascading down levels of referential id columns..) Should do the trick. Andrew -----Original Message----- From: Johnny Withers [mailto:joh...@pixelated.net] Sent: 11 May 2009 22:10 To: Weston, Craig (OFT) Cc: MySQL General List Subject: Re: Merging Databases We don't want to use a view because then this database will not be consistent with the others. We can't simply use a select from .. insert into because when we renumber table1's ID column, items in table2 and 3 and so on may link to the ID column in that table. So we need to update the ID column in table1, then add the same # to the table1_id columns in any other table. After we do this, we could do the select from.. insert into method I suppose. -jw On Mon, May 11, 2009 at 2:49 PM, Weston, Craig (OFT) < craig.wes...@oft.state.ny.us> wrote: > > > -----Original Message----- > From: Johnny Withers [mailto:joh...@pixelated.net] > Sent: Monday, May 11, 2009 3:30 PM > To: MySQL General List > Subject: Merging Databases > > Hi, > I have a lot of databases that have the exact same tables and each table > has > the exact same column structure. I'm looking at merging two of these > databases together into a single database (Company A bought Company B and > wants the data from A combined into B now). > > I've been tossing around the idea of looking in database B at each table > that would need to be "merged" and simply adding the last ID number to > every > ID number in database A's tables. For example, in table1 in B's data, the > last ID number is 2000, could we simply add 2000 to every ID number in > table1 in A's data? Could we then export (SELECT INTO OUTFILE) from A's > data > and import (LOAD DATA) into B's data? > > Has anyone done something like this before? Did you have problems? > > -- > ----------------------------- > Johnny Withers > 601.209.4985 > joh...@pixelated.net > > --- > > Why not create a view and just concatenate on an identifier? This way the > data can be kept in the same forms. > > Or, if you do want to have it as one table, you can use a select insert > statement to move from one to another. Build the select query first to get > the data looking like you want it, then convert it when you think you are > ready. > > Of course, backups are your friend in any case. :) > > > Cheers, > Craig > > > This e-mail, including any attachments, may be confidential, privileged or > otherwise legally protected. It is intended only for the addressee. If you > received this e-mail in error or from someone who was not authorized to send > it to you, do not disseminate, copy or otherwise use this e-mail or its > attachments. Please notify the sender immediately by reply e-mail and > delete the e-mail from your system. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net > > -- ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
