SQL. When I trying to do something like "INSERT INTO Table VALUES(x1,x2,x3) - can the x1 x2 x3 be sql queries, like SELECT <...>

Question

I want to do something like this:

QSqlQuery q;
q.prepare("insert into Norm values(select from Disc id_disc WHERE name_disc=?, select from Spec code_spec WHERE name_spec=?,?");
q.addBindValue(MainModel->data(MainModel->index(MainModel->rowCount()-1, 1)).toString());
q.addBindValue(ui->comboBox->currentText());
q.addBindValue(MainModel->data(MainModel->index(MainModel->rowCount()-1, 2)).toString());
q.exec();

But it's not working. Surely for someone obviously where is the error and maybe he tells me how to do it right.

Sql is not my strongest but after prepare, should the "incert" be "insert"? — Bjorn, Nov 27 '13 at 08:04

score 1 · Accepted Answer · edited Nov 27 '13 at 08:34

1

First of all your you have done spelling mistake. Its "INSERT" not "INCERT" And yes we can insert SELECT query inside INSERT query. eg:

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

edited Nov 27 '13 at 08:34

Marius

15,148
9
56
76

answered Nov 27 '13 at 08:06

Swass

310
2
4
11

score 1 · Answer 2 · answered Nov 27 '13 at 08:51

INSERT ... SELECT ... is used when you want to insert multiple records, or when most values to be inserted come from the same record.

If you want to insert one record with values coming from several tables, you can use subqueries like you tried to do, but you have to use the correct syntax: scalar subqueries must be written inside parentheses, and you must write the SELECT correctly as SELECT value FROM table:

INSERT INTO Norm
VALUES ((SELECT id_disc FROM Disc WHERE name_disc = ?),
        (SELECT code_spec FROM Spec WHERE name_spec = ?),
        ?)

score 0 · Answer 3 · answered Nov 27 '13 at 08:37

0

If you want data from two tables, you must first write a query which return pretended data - using JOIN, UNION, subqueries, ...

Then, just do

INSERT INTO target_table SELECT ...

answered Nov 27 '13 at 08:37

LS_ᴅᴇᴠ

10,823
1
23
46

SQL. When I trying to do something like "INSERT INTO Table VALUES(x1,x2,x3) - can the x1 x2 x3 be sql queries, like SELECT <...>

3 Answers3