copy a table to another and add a column that is the tablename of the source

I want to copy some tables who all have the same prefix into one table. The destination table should have on column more then the source, that is the name of the source table.

I tried this :

INSERT INTO `tbl_dest` (`nr`,`n1`,`n2`,`n3`,`n4`,`n5`,`table`) 
SELECT `a`.`nr`, `a`.`n1`, `a`.`n2`, `a`.`n3`, `a`.`n4`, `a`.`n5`, `b`.`table`  
FROM `tbl_src` `a`, `INFORMATION_SCHEMA.TABLES`.`TABLE_NAME` `b`

but it is not working well.
Practically in the tbl_dest the value of the column table should be ‘tbl_src‘ for all data originating from tbl_src

Any idea?

Answer

You can use a constant:

INSERT INTO `tbl_dest` (`nr`,`n1`,`n2`,`n3`,`n4`,`n5`,`table`) 
    SELECT `a`.`nr`, `a`.`n1`, `a`.`n2`, `a`.`n3`, `a`.`n4`, `a`.`n5`,
       'tbl_src' 
    FROM tbl_src a;

I would also advise you to dispense with all the backticks. They just make the query harder to write and to read.