SQLITE - insert question

Question

Hi,

I've got a list of items in a certain table and I want to have another table with an ID from the first table and another constant values in other columns.

I've got the following query:

INSERT INTO table02(item_id, status, card_status) VALUES( (SELECT item_id FROM table01), -1, 10);

But it works only for the first row.

How can I make it work for all rows? I need to copy all IDs from table01 and assign to them values -1 and 10 in other columns.

Thanks.

Answer 1

In ANSI SQL it would be:

INSERT INTO table02(item_id, status, card_status)
SELECT item_id, status, card_status FROM table01;

where status and card_status are your hard coded values.

...so I guess that would work here too.

Answer 2

INSERT INTO table02 (item_id, status, card_status) SELECT item_id, -1, 10 FROM table01;

Answer 3

I would create table02 with defaults and then insert only the item_id from table01.

CREATE TABLE table02 ( item_id INTEGER PRIMARY KEY,
                       status INTEGER DEFAULT -1,
                       card_status INTEGER DEFAULT 10);
INSERT INTO table02 (item_id) SELECT item_id FROM table01;