How do i do an SELECT in an INSERT?

Question

I want to do something like this

INSERT INTO link_list(link, status, hash_type, hash_id)
VALUES(@link, @status, @hash_type, @hash_id);
INSERT INTO active_dl(fileId, orderNo)
VALUES(last_insert_rowid(), SELECT COUNT(*) FROM active_dl);

But obviously it is wrong and theres a syntax error on select. How do i write this? Using sqlite.

Answer 1

Put the SELECT query in brackets parenthesis.

Like so:

INSERT INTO active_dl(fileId, orderNo)
VALUES(last_insert_rowid(), (SELECT COUNT(*) FROM active_dl));

Answer 2

Why don't you try:

DECLARE @var INT
SET @var = (Select Count(*) from active_dl)

INSERT INTO active_dl(fileId, orderNo)
VALUES(last_insert_rowid(), @var);

Answer 3

have you tried maybe:

...
VALUES(last_insert_rowid(), (SELECT COUNT(*) FROM active_dl)); <--- with parens

I don't claim to know sqlite, but maybe this could help

Answer 4

I believe standard SQL lets you use either values or select for an insert, so I'd be looking at:

insert into active_dl(fileId, orderNo)
    select last_insert_rowid(), COUNT(*) FROM active_dl;

Whether SQLite allows this I don't know for sure, hence the community wiki.