Solved

How do I use a SELECT with an INSERT statment?

Posted on 2007-12-05
4
197 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Suggested Courses

710 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