sqlite insert into table select * from

Solution 1:

explicitly specify the column name in the INSERT clause,

INSERT INTO destinationTable (risposta, data_ins)
SELECT STATUS risposta, DATETIME('now') data_ins 
FROM   sourceTable

Solution 2:

This might help, a query from one table to another and it will also check if the selected column (id) already exist in another table.

SQLite QUERY:

INSERT INTO MYTABLE2(id,data_ins ) 
SELECT id, data_ins FROM MYTABLE2
WHERE id NOT IN ( SELECT id FROM MYTABLE1)

Android:

 String select_insert_query = "INSERT INTO " + TABLE_MYTABLE2
            + "( " + ID + "," + DATA_INS + ") SELECT "
            + ID + ","
            + DATA_INS + " FROM "
            + TABLE_MYTABLE2
            + " WHERE " + ID + " NOT IN (SELECT " + ID
            + " FROM " + TABLE_MYTABLE1 + ")";

    SQLiteDatabase db = this.getWritableDatabase();

    Cursor cursor = db.rawQuery(select_insert_query, null);
    cursor.close();