mysqlmariadbcopysql-insert

Copy table to another table with an extra column at the start

In the past, I have successfully copied tables from one to other like this:

INSERT INTO myhistory SELECT p.*, NOW(), '' FROM mymain p

Here, the extra columns were at the end. However, I now have a different case where the extra column is (a) at the beginning of the table and (b) it is an auto-incremented column. So I tried this:

INSERT INTO `starchive` null, SELECT f.* FROM `stflagged` f

This isn't working. I also tried 0 instead of null, but that fails too. Any ideas?

Solution

  • The NULL is part of the select statement so the number of fields match. So:

    INSERT INTO `starchive` SELECT NULL, f.* FROM `stflagged` f

    Or explicitly list table columns:

    INSERT INTO `starchive` (col2, col3, col4) SELECT f.* FROM `stflagged` f

    example ref: https://dbfiddle.uk/5vKYKdwC