How to import data from one table to other

[gurmeet]

Hello friends


i have a four tables, their fields and data types are same...
now i wnat all tables' record into one table....

i have t1, t2, t3, t4 tables...
either suggest me the way to import all tables (t2,t3,t4) data into t1 table or
all tables data (t1,t2,t3,t4) data into a new table...



thanx in adv

[timbim]

What's the data structure of the four tables? Why do you want to merge them?

[james438]

I am going to be lazy in my answer.

<?php
include 'include/db.php';
$query = "SELECT * FROM news";
$result = mysql_query($query,$conn) or die ("Couldn't execute query.");
$numrows = mysql_num_rows($result);
$query = "SELECT * FROM news2";
$result1 = mysql_query($query,$conn) or die ("Couldn't execute query.");
while ($row = mysql_fetch_array($result1,MYSQL_ASSOC)){
$ID=$row['ID'];$ID=$ID+numrows+1;
$marriage=$row['energy'];$marriage=addslashes($marriage);
$date=$row['date'];
$db="INSERT INTO news (ID,marriage,date) VALUES ('$ID','$marriage','$date')";
$result = mysql_query($db) or die (mysql_error());
}
echo "all done";
?>

When combining tables make sure you are not mixing the data types of your columns, for example try not to put your text document into a datetime column. Make sure you backup your database as well or at least the tables you are working with.

The above script will take the content from the "news2" table and add it to the end of the table "news". The line that says $marriage=$row['energy']; is where a column of one name is changed to the correct name. It is not necessary, but I did it this way, because it made more sense to me this way.

The above is just an old script I had lying around. I have not had the need to use it in quite a while now.

The above should be enough for you to do what you need.

[gurmeet]

INSERT INTO t1 SELECT * FROM t2


is the best way...... use it when u need it

[james438]

That does indeed work gurmeet and is great to know, however it does not address duplicate values. For example if you have:

Table=t1
ID=3 col1=red

Table=t2
ID=3 col1=green

Here you will get an error. What's worse is when it does work, but your data is in the wrong columns. You can use this if you know what you are doing.