Move data from 1 database to another

[daveoffy]

I have 2 databases. I want to move all data from members on DB1 to profile on DB2. How will I do that?

[Mchl]

You dump the data from one database and load it into the other.

 

Want more specific answer, ask more specific question.

[elgoog]

If you are using phpMyAdmin, the operations tab can copy data and structure  if the db is on the same server.... otherwise you can use the export and import tabs

[daveoffy]

I have 2 databases. Named DB1 and DB2. In database DB1 I have a table named members with lets say 10 members. In DB2 I have a table named profile with no members. How can I move the rows in members from DB1 to the profile table in DB2?

[Mchl]

Are both tables identical in structure or do they differ?

[daveoffy]

They differ a little. How will I move one thing over at a time than?

[Mchl]

I would try exporting rows from first database into a CSV file.

Edit the file (either by hand if small, or by custom script if large) so that it fits into second table.

Import the file into second table using LOAD DATA INFILE

[elgoog]

Using PhpMyAdmin you can

 

Go to the members table

Go to Export

Export for CSV MS Excel

In the options put the field names in first row

tick save as file and save to your computer

 

Do the same for the profile table

The just copy and paste the columns from the members spreadsheet to the profile spreadsheet.

 

When you have the file as you need, then delete the column names from the first row

 

 

Import the data back in by going to the profile table and clicking import tab

 

You can import using CSV LOAD Data, and in fields terminated by, replace ; with a comma (,).

 

 

[daveoffy]

I have DB1 table members, DB2 with table profile. Both have username columns. I want to move all the members usernames to the table profile in DB2 usernames. How will that be done? I am writting a PHP script for this, I don't have PHPMYADMIN for this job.

[elgoog]

you would select the records. and run through in a loop saving the info you need from DB1 to columns in DB2

 

 

<?php

$query = "SELECT * FROM DB1";

 

$result = mysql_query($query) or die(mysql_error());

 

while($row = mysql_fetch_array($result)){

 

 

mysql_query("INSERT INTO DB2

(username) VALUES($row['username']) ")

or die(mysql_error()); 

 

}

?>

 

[daveoffy]

that is just moving data from tables. I want to move from different databases. I have 2k users to move.

[elgoog]

same theory.. But you will have to make 2 seperate database connections. if you are using a connection class, this would be easier. Select from one, loop through and write to the other.

[npsari]

An easy way of doing that is by simply backing up your database first, which means, you have to back up the whole database or all the tables in that database... Then, you create the new database and restore the data which you backed up.

 

If you dealing with mySQL, then, you can use 'phpmyadmin' which does it easily, you simply open the database there and click on 'Export', then click on 'Restore' to save the data in the new database