How do I use a SELECT with an INSERT statment?

I wish to insert a series of rows into a table. The rows are obtained as a result of an intersection between two other tables. For example:

INSERT INTO A values (b_id,'xxx','yyy',...)
...
SELECT b_ id
FROM b
WHERE b_bill_date = to_date('04-DEC-2007','DD-MM-YYYY')
INTERSECT
SELECT DISTINCT c_id
FROM c, d
WHERE c_term_code = '200720'
AND d_rsts_code = d_code
AND d_incl_sect_enrl = 'Y';

What is the correct syntax for this query?
geeta_m9Asked:
Who is Participating?
 
imitchieConnect With a Mentor Commented:
Try this. As the other expert has mentioned, you can use constants in selects
INSERT INTO A values (b_id,f1,f2,...)
SELECT b_ id, 'xxx','yyy',....
FROM b
WHERE b_bill_date = to_date('04-DEC-2007','DD-MM-YYYY')
INTERSECT
SELECT DISTINCT c_id
FROM c, d
WHERE c_term_code = '200720'
AND d_rsts_code = d_code
AND d_incl_sect_enrl = 'Y';

Open in new window

0
 
Patrick MatthewsCommented:
INSERT INTO TableA (column1, column2, column3)
SELECT x, y, z
FROM <one or more joined tables>
0
 
geeta_m9Author Commented:
Only the values in column A will be obtained from the result of the intersection. The columns in the other values are going to be fixed, i.e. hardcoded in.
0
 
randomjamesCommented:
For hard-coded values in fields instead of a field name just put the value in single quotes for text or date and just put the number you need if it's a number.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.