Results 1 to 3 of 3
Feb 5, 2011, 09:53 #1
- Join Date
- Sep 2005
- 2 Post(s)
- 0 Thread(s)
Merge tables into one and add new column at the same time
I have lots of tables with identical structures. I want to put them into one single table. Primary key will be updated, so I'm SELECTing everything else. However, I want to have a new column in the new table. The value of this column should contain the name of the old table.
So, all the rows from "oldtable1" should be moved into new_table, and the "old" column should have the value "oldtable1", for these rows.
I tried this but it doesn't work. I'm doing it in PHP, where $name is the name of the old table, and this is part of a loop where $name changes with each iteration.
CREATE TABLE IF NOT EXISTS $new_table ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, phone VARCHAR(255) NOT NULL, timestamp INTEGER NOT NULL, quantity TINYINT(1) NOT NULL DEFAULT 0 ) ENGINE = MyISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci; INSERT INTO $new_table (name, email, phone, timestamp, quantity) SELECT name, email, phone, timestamp, quantity FROM $name SET old=$name;