sqlite insert into table select * from

Question

I need to move data from one table to another in my Android app

I would like to use the following sql:

insert into MYTABLE2 select id, STATUS risposta, DATETIME('now') data_ins from  MYTABLE 2

Unfortunately in table MYTABLE2 there is an _ID column AUTOINCREMENT. What could I do?

Thanks.

EDIT: this is my MYTABLE2 the, the table I would like to populate with data from another table:

CREATE TABLE "ANSWERS" ("_id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
"ID_QUESTION" INTEGER,"DATE_INS" DATETIME DEFAULT 
(CURRENT_DATE) , "ANSWER" INTEGER)

score 91 · Accepted Answer · answered May 30 '13 at 14:32

91

explicitly specify the column name in the INSERT clause,

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

answered May 30 '13 at 14:32

John Woo

258,903
69
498
492

1

#JW 웃 If I do this it says: MYTABLE2 has x columns but y values were supplied. Where y =x+1. It seems _id primary key autoincrement is needed as well – Gyonder May 30 '13 at 14:41
it will generate an exception. the number of values must match with the number of columns specified. – John Woo May 30 '13 at 14:43
1

you can pass `NULL`, this is a simplified example, `INSERT INTO destTable(DestinationID, OtherID, ColA) SELECT NULL, OtherID, COlA, FROM SourceTable` – John Woo May 30 '13 at 14:52

score 6 · Answer 2 · answered Dec 13 '17 at 12:08

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();

score 5 · Answer 3 · answered May 30 '13 at 14:33

5

You can specify which columns you're inserting into. Assuming that the _id column is autoincrement and you're inserting the other two columns, you can have something like this:

insert into MYTABLE2 (riposta, data_ins)
select STATUS risposta, DATETIME('now') data_ins from  MYTABLE 2

answered May 30 '13 at 14:33

Aleks G

56,435
29
168
265

#Aleks G If I do this it says: MYTABLE2 has x columns but y values were supplied. Where y =x+1 – Gyonder May 30 '13 at 14:39
@Gyonder Are you sure you removed your `id` column from the select clause? – Aleks G May 30 '13 at 14:58

sqlite insert into table select * from

3 Answers3

Linked