Adding Mysql columns from one table to another

Question

I have two MySQL tables: one called Audit and the other Audit2. I need to copy the column names from Audit2 to Audit. For example table Audit has columns 1, 2 and 3 and Audit2 has columns 4, 5 and 6 and I need Audit to have columns 1, 2, 3, 4, 5 and 6.

I tried the following to no success:

ALTER TABLE  `Audit` ADD  (select `COLUMN_NAME` from information_schema.columns
where table_schema = 'BMS' and `TABLE_NAME` = 'Audit2') (select `DATA_TYPE` from information_schema.columns
where table_schema = 'BMS' and `TABLE_NAME` = 'Audit2') NOT NULL

MySQL doesn't have such feature. Just use a GUI tool that implements it, e.g. HeidiSQL. You don't need to do it programmatically, do you? — Álvaro González
– Álvaro González, Commented Aug 13, 2015 at 10:18
Thanks will try it out. No need to do it programmatically just once — elstiv
– elstiv, Commented Aug 13, 2015 at 10:21

Madhivanan · Accepted Answer · 2015-08-13 10:39:18Z

1

You can do it by

create table new_audit as
select t1.*,t2.* from audit as t1 inner join audit_2 as t2 on 1=0;

The table new_audit will have all the columns

answered Aug 13, 2015 at 10:39

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ritobroto Mukherjee · Accepted Answer · 2015-08-13 10:30:42Z

0

Have you tried:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='your_table_name';

It'll return all the column names in a single column from the table you want.

answered Aug 13, 2015 at 10:30

Ritobroto Mukherjee

1111 silver badge13 bronze badges

Comments

Jonathan Clark · Accepted Answer · 2015-08-13 10:40:12Z

0

If you have a PHP environment set up, you can download this tool: http://www.mysqldiff.org/downloads.php It's brilliant - will compare two mysql database schema's and generate the mysql code that will sync the structures together, sounds just like what you need and handy for the future too.

If not. I'd do just what you're doing, but probably just simplify it a bit for the sake of a one off instance and 3 columns.

Something like ALTER TABLE Audit ADD yourcolumnname VARCHAR(100);

answered Aug 13, 2015 at 10:40

Jonathan Clark

1,2701 gold badge9 silver badges28 bronze badges

Collectives™ on Stack Overflow

Adding Mysql columns from one table to another

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related