Duplicate a MySQL table – Copy Table / Duplicate Database / PHP Script


Suppose if you want to copy a Table to another, this can be done in MySQL with two steps. The first one is to copy the table structure, and the second step is to fill in the data with the original table.

Copy the Table Structure

The syntax is straightforward in MYSQL.

1
Create Table `NewTable` Like `OldTable`;
Create Table `NewTable` Like `OldTable`;

Copy the Data to New Table

1
Insert Into `NewTable` Select * From `OldTable`;
Insert Into `NewTable` Select * From `OldTable`;

Duplicate Database

The above shows how to copy/duplicate one single table. So to duplicate/copy entire database, you would need the following to show all tables in a single database:

1
Show Tables;
Show Tables;

Then, write a script in any programming language to copy each single table. For example, the following is the PHP script that copies all tables one by one as given by ‘Show Tables’ result:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
// helloacm.com
 
function duplicate($originalDB, $newDB) {
    $db_check = @mysql_select_db ( $originalDB );
    $getTables =  @mysql_query("SHOW TABLES") or return(mysql_error());   
    $originalDBs = [];
    while($row = mysql_fetch_row( $getTables )) {
            $originalDBs[] = $row[0];
        }
    @mysql_query("CREATE DATABASE `$newDB`") or return(mysql_error());
    foreach( $originalDBs as $tab ) {
            @mysql_select_db ( $newDB ) or return(mysql_error());
            @mysql_query("CREATE TABLE $tab LIKE ".$originalDB.".".$tab) or return(mysql_error());
        @mysql_query("INSERT INTO $tab SELECT * FROM ".$originalDB.".".$tab) or return(mysql_error());
    }
    return true;
}
// helloacm.com

function duplicate($originalDB, $newDB) {
	$db_check = @mysql_select_db ( $originalDB );
	$getTables =  @mysql_query("SHOW TABLES") or return(mysql_error());   
	$originalDBs = [];
	while($row = mysql_fetch_row( $getTables )) {
    		$originalDBs[] = $row[0];
    	}
	@mysql_query("CREATE DATABASE `$newDB`") or return(mysql_error());
	foreach( $originalDBs as $tab ) {
    		@mysql_select_db ( $newDB ) or return(mysql_error());
    		@mysql_query("CREATE TABLE $tab LIKE ".$originalDB.".".$tab) or return(mysql_error());
		@mysql_query("INSERT INTO $tab SELECT * FROM ".$originalDB.".".$tab) or return(mysql_error());
	}
	return true;
}

–EOF (The Ultimate Computing & Technology Blog) —

GD Star Rating
loading...
281 words
Last Post: MySQL server stopped due of out of memory exception on Ubuntu VPS
Next Post: How to Print Pascal Triangle in C++ (with Source Code)

The Permanent URL is: Duplicate a MySQL table – Copy Table / Duplicate Database / PHP Script

15 Comments

  1. Arshal

Leave a Reply