Can't insert all the records from one table into another

I have two tables:

CREATE TABLE new_orders
(
    clientId INTEGER NOT NULL,
    exchangeId NOT NULL,
    marketId NOT NULL,
    id INTEGER NOT NULL,
    accountType NOT NULL,
    listId INTEGER NOT NULL,
    clientGuid NOT NULL,
    side NOT NULL,
    type NOT NULL,
    status NOT NULL,
    price NOT NULL,
    stopPrice NOT NULL,
    amount NOT NULL,
    filled NOT NULL,
    cost NOT NULL,
    createTime NOT NULL,
    updateTime NOT NULL,
    PRIMARY KEY(clientId)
)

CREATE TABLE old_orders
(
    exchangeId NOT NULL,
    marketId NOT NULL,
    id INTEGER NOT NULL,
    accountType NOT NULL,
    listId INTEGER NOT NULL,
    clientId NOT NULL,
    side NOT NULL,
    type NOT NULL,
    status NOT NULL,
    price NOT NULL,
    stopPrice NOT NULL,
    amount NOT NULL,
    filled NOT NULL,
    cost NOT NULL,
    createTime NOT NULL,
    updateTime NOT NULL,
    PRIMARY KEY(accountType, marketId, id) WITHOUT ROWID
)

When I try to insert all the records from old_orders to new_orders and generate new clientId automatically with the following query:

    INSERT INTO new_orders 
        SELECT clientId AS clientGuid, exchangeId, marketId, id, accountType, listId, side, type, status, price, stopPrice, amount, filled, cost, createTime, updateTime 
        FROM old_orders;

but the query fails with the following error:

Table new_orders has 17 columns but 16 values were supplied

How to make this work?

new_orders.clientId is an autoincrement column, right? So why does this query not work?

Solution

When using an INSERT statement, always specify the names of the columns you are inserting into:

 INSERT INTO new_orders (clientGuid, exchangeId, marketId, id, accountType, listId, side, type, status, price, stopPrice, amount, filled, cost, createTime, updateTime)
        SELECT clientId AS clientGuid, exchangeId, marketId, id, accountType, listId, side, type, status, price, stopPrice, amount, filled, cost, createTime, updateTime 
        FROM old_orders;

If you don't specify the list of columns being inserted into, then it is assumed you are inserting into all columns. new_orders has 17 columns, but you are selecting only values for 16 columns. It doesn't matter that one column is an auto-increment column, it will still be considered to be one of the 17 columns being inserted into.

Furthermore, when no list of columns is specified, the order of the columns inserted into is the order in which they appear in the table. The columns in your tables are not in the same order, so even if you didn't have a problem with the number of columns, you would either get an error about mismatching datatypes, or data would end up in the wrong columns.