MySQL - Copying partial data from one table to another

This may be a silly question, and I understand why I'm getting the result that I am, however, I thought mySQL acted differently and I can't finish the documentation to tell me otherwise.

I have 2 basic tables as follows:

CREATE TABLE test ( num INT, time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP );

CREATE TABLE test_to_copy ( num INT );

I then create a single entry into the test_to_copy table: INSERT INTO test_to_copy VALUES ( 12 );

Now I try and copy the table test_to_copy to test like so: INSERT INTO test SELECT * FROM test_to_copy;

The error that keeps getting thrown is

"Column count doesn't match value count at row 1".

I know that it is complaining that the number of columns in both tables does not match meaning it does not know what variable we are assigning our copy to, however, should it not be a case where the time is created automatically i.e. defaulted if nothing is inserted when we do the copy rather than throw the error?

Due to constraints, I can no longer have the time in both tables, and I must do a SELECT * on the test_to_copy table as there are over 50 columns, and i'm wondering is there an easy way around this?

Solution

This is another variation of a frequent question: "can I query *-except-for-one-column?"

No, there is no wildcard-with-exceptions syntax in SQL. The * wildcard means all columns. If you don't want all columns, you must name the columns explicitly.

If you have a variety of columns because this method may be used for more than one table, you can get the list of columns for any given table from INFORMATION_SCHEMA.COLUMNS and use that information to build a dynamic SQL query.

Here's a way you can produce the list of columns:

SELECT 
  GROUP_CONCAT(
       CONCAT('`', column_name, '`')
  ) AS _cols
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='mydatabase' AND TABLE_NAME='mytable'
  AND COLUMN_NAME NOT IN ('time'); -- or other columns to exclude