mixing 2 databases?

I he a website which runs on 1 type of software with a MySQL backend, and im contemplating buying another which runs on different type of software, i can probably get something that will convert the new site other to my existing one, however i then want to add it all together in order to make 1 big database.

The database is made up of vrious things but includes users/posts etc and so im thinking some things may overlap possibly post numbers, userumbers etc, is there any way what i want can be done?

Cheers

forgot to mention if you can definately do this please post price

 

This is a pretty long explanation of how you can do this. Note, however, that it is a bit simplified.

For this example we will use four tables. We will use the user table and the posting table from each system. The actual table you have may be very different than what I am listing.

System A
-----------------------
tblUserA
userID int primary key
userName char(25)
password char(25)
emailAddress char(25)
joinDate dateTime

tblPostingA
postingID int primary key
userID int foreign key
postingDate dateTime
postingText char(4000)

-----------------------
System B
-----------------------
tblUserB
userID int primary key
userName char(25)
password char(25)
emailAddress char(25)
joinDate dateTime

tblPostingB
postingID int primary key
userID int foreign key
postingDate dateTime
postingText char(4000)

The first step is to move the System B tables over to the System A database. Before you do this there are a few things to take into account.

1) Tables in System B that have the same name as tables in System A will need to be renamed either before or when you move them.

2) Different databases may have unique data types or they may express data type differently. After you move the System B tables, check them to make sure the data maintained the same values. If both databases are the same (both MySQL or both MSSQL) this part should be pretty smooth. If they are different, you need to make sure the data types match up.

3) Check field lengths on the data types where this applies. You cannot shove a char(30) into a char(25) without clipping.

The next thing is to look at your tables and determine the order. In this example, the user table is the first table you want to work on. The reason for this is that it has no foreign keys in it. Then you would work on the posting tables as they have the foreign key of userID.

You have two choices here. You can simply add the data from System B to System A's table, or you can create a new table. I would suggest a new table. It will allow you to keep the records in chronological order.

Now, create a table for temporary use. This table will have the same columns and a couple new ones. Remember, if your field length values are different, use the longer one. (e.g. if Sys A userName is a char (25) but Sys B userName is a char(30) then use a char(30))

tmpUser
userID int primary key
userName char(25)
password char(25)
emailAddress char(25)
joinDate dateTime
oldID int
systemOfOrigin char(1) -- this field can be anything you want it to be (int, char, etc...)

Now you would run some insert scripts:

Insert Into tmpUser
(userName, password, emailAddress, joinDate, oldID, systemOfOrigin)
Select userName, password, emailAddress, joinDate, userID, 'A' From tblUserA

Insert Into tmpUser
(userName, password, emailAddress, joinDate, oldID, systemOfOrigin)
Select userName, password, emailAddress, joinDate, userID, 'B' From tblUserB

The next step is to create the table that you will actually use.

tblUser
userID int primary key
userName char(25)
password char(25)
emailAddress char(25)
joinDate dateTime
oldID int
systemOfOrigin char(1) -- this field can be anything you want it to be (int, char, etc...)

Now insert the records from the temporary table in the order that you want them.

Insert Into tblUser
(userName, password, emailAddress, joinDate, oldID, systemOfOrigin)
Select userName, password, emailAddress, joinDate, oldID, systemOfOrigin From tmpUser Order By joinDate

Now you have your new user table with the users in the correct chronological order. Two things to note: the userID field is autopopulated, so you won't be putting any data in that field (the database does that for you); also you will want to check the data to make sure that everything worked right.

Now, before you merge the posting tables, you need to do some work to them. Add a column to the posting tables for both systems. This column will be an int field called newUserID.

Update each table like so:

Update tblPostingA a
Set newUserID = (select userID from tblUser u where u.systemOfOrigin = 'A' and u.oldID = a.userID)

Update tblPostingB b
Set newUserID = (select userID from tblUser u where u.systemOfOrigin = 'B' and u.oldID = b.userID)

*** I am not sure if this will work in MySQL. I mainly use MSSQL and it works there.

Now create a temporary table for the postings

tmpPosting
postingID int primary key
userID int
postingDate dateTime
postingText char(4000)

You will note that we do not have an oldID field or a systemOfOrigin field in this table. The reason is that in this example, this is the last table in the sequence. There is no need to have this data. If there were a table that used the postingID as a foreign key, you would need to record that data.

Insert the data from the other tables with code like this:

Insert Into tmpPosting
(userID, postingDate, postingText)
Select newUserID, postingDate, postingText From tblPostingA

Insert Into tmpPosting
(userID, postingDate, postingText)
Select newUserID, postingDate, postingText From tblPostingB

Create the final table:

tblPosting
postingID int primary key
userID int foreign key
postingDate dateTime
postingText char(4000)

Now insert the data:

Insert Into tblPosting
(userID, postingDate, postingText)
Select userID, postingDate, postingText From tmpPosting

Now check all the data. If the data is fine, and everything matches up, you can remove the temporary tables and old tables from your database.

Keep in mind, before you merge a set of tables (and by set I mean corresponding tables - userA + userB is a set) that has a foreign key in it, you will need to merge the tables that those foreign keys come from.

This is simplified and does not dicuss the issues of table sets with different fields. You may have to do some conversion of fields if data types do not match.

There may be tools to do this, but I always do it by hand.

*** The code in this has not been tested and may need a little tweaking, but the concept is all there.