Copy all table contents and add to another in same db

[ski442]

Hi Guys.

How would i copy all data from 1 table to another. all fields are the same and in the same db.

I have tried to use the Export feature and save to my pc and then use the insert data feature but can not do it, as i can save the export in file in SQL, LaTeX, CSV for MS Excel data, CSV data, XML

My db is direct on my server throu My Panel and sitecontroll, i have 30 tables and want to copy all tables in to one table.

Thanks Ski442

[SteveLetwin]

If all the tables have the same structure, then something of the form

insert into dest_table select * from source_table

should work. And just run it once for each source table. If the columns are in different orders or if you have extra columns then you might have to be more explicit and list the column names and such. And I'm not sure what would happen with auto-incrementing primary keys. But that general form is what you're looking for I think.

If you're using mysql, then here's the relevant documentation: [dev.mysql.com...]

[Habtom]

and then use the insert data feature but can not do it

Do you mean the import option? If you haven't tried importing it, do that. This should do it for you.

Habtom

[ski442]

Thanks for your replies guys.

As i am very new to php and mysql I do not know how to use code given from SteveLetwin. Do I need to build a page on my site with this code, like an admin page maybe.

Thanks Habtom.
There is a button called "Insert data from a textfile into table" then when I use this it will either insert nothing or a whole load of empty rows. Maybe saving date as wrong format.

Very sorry for my lack of knowledge guys, finding php very hard to sink in me head. A few more pointers will be good.

Thanks guys.

[Habtom]

You can do it in many ways, but let me show you this:
While Exporting: (I am assuming you are using phpMyAdmin for MySQL DB)

> Go to that Specific Table
> Export Option
> Select the SQL Export option
> Save as file

And save the file

While Importing the same file to a different db:

> Import Option (Not the Insert)
> Location of the text file - Browse for it
> Format of imported file - SQL

Habtom