Solved

How do I use a SELECT with an INSERT statment?

Posted on 2007-12-05
4
201 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

PowerShell Core for Advanced Linux Administrators

Understand advanced principals around Powershell Core with a focus on the Linux Administrator.  This course covers how to administer numerous environments across multiple platforms including Linux, Azure, AWS, and Google Cloud from a single shell instance.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

617 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