Solved

How do I use a SELECT with an INSERT statment?

Posted on 2007-12-05
4
172 Views
Last Modified: 2010-03-20
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?
0
Comment
Question by:geeta_m9
4 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 20413075
INSERT INTO TableA (column1, column2, column3)
SELECT x, y, z
FROM <one or more joined tables>
0
 

Author Comment

by:geeta_m9
ID: 20413091
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
 
LVL 3

Expert Comment

by:randomjames
ID: 20413553
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
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20416639
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

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
A short film showing how OnPage and Connectwise integration works.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now