Merge tables into one and add new column at the same time

Databases
1

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;

It’s the SET old=$name bit at the end that’s breaking it. I’ve thought about simply altering the old table with the new column, putting in the value into every row of that column and then doing the move, but I’m wondering if there’s a simpler way.

2

You can do it like this:


INSERT INTO $new_table (
   name
 , old
 , email
 , phone
 , TIMESTAMP
 , quantity
)
SELECT
   name
 , "$name"
 , email
 , phone
 , TIMESTAMP
 , quantity
FROM
   $name

Basically you just define a column with a constant value of $name

3

Doh. So simple. :slight_smile: Thanks!