Can you insert with a mix of of subquery and values?

Question

I have a procedure that is receiving some values from our application, I need to insert these static values and the results of a subquery to one of our tables. Is this possible with one insert statement? So it would look like this

Insert into table
values 
(value1, value2, value3, (select testdata from dual));

The resulting table would look like:
value1 | value2 | value 3 | ROW 1 FROM SUBQUERY
value1 | value2 | value 3 | ROW 2 FROM SUBQUERY

I know that if I was only using a subquery I would omit the values command and vice versa with actual values. Does anyone have experience with this one?

score 4 · Accepted Answer · answered Jul 19 '13 at 20:32

4

Yes, but you do it only with a select:

Insert into table
    select value1, value2, value3, testdata
    from dual;

That is, you add the values as constants on the select line.

answered Jul 19 '13 at 20:32

Gordon Linoff

1,242,037
58
646
786

Would this work if my values (value1, value2, value3) are IN variables and my testdata is a multi-row and multi-column subquery? – Quartzee Jul 19 '13 at 20:56
@user2600900 . . . Yes and yes. – Gordon Linoff Jul 19 '13 at 21:07

score 0 · Answer 2 · answered Jul 19 '13 at 20:36

0

Use INSERT INTO tablename SELECT xxx FROM command.

INSERT INTO tablename ( col1, col2, ... colN)
-- select rows from first subquery
SELECT x1, x2, ... xN FROM ......
UNION ALL
-- select rows from second subquery
SELECT y1, y2, ... yN FROM ......

answered Jul 19 '13 at 20:36

krokodilko

35,300
7
55
79

Can you insert with a mix of of subquery and values?

2 Answers2