sqlite插入表select * from

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

我需要在我的Android应用中将数据从一个表移动到另一个表

I would like to use the following sql:

我想使用以下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?

不幸的是，在表MYTABLE2中有一个_ID列AUTOINCREMENT。我能做什么？

Thanks.

谢谢。

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

编辑：这是我的MYTABLE2，我希望用另一个表中的数据填充表格：

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

3 个解决方案

#1

explicitly specify the column name in the INSERT clause,

在INSERT子句中显式指定列名，

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

#2

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:

您可以指定要插入的列。假设_id列是自动增量并且您要插入其他两列，您可以使用以下内容：

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

#3

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.

这可能会有所帮助，从一个表到另一个表的查询，它还将检查所选列（id）是否已存在于另一个表中。

SQLite QUERY:

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

#1