Home » SQL & PL/SQL » SQL & PL/SQL » INSERT query
INSERT query [message #403799] |
Mon, 18 May 2009 15:34 |
kishorep
Messages: 1 Registered: May 2009
|
Junior Member |
|
|
Hi Gurus:
I have a table with columns (ID is the primary key) --
ID | Q1 | R1 | Q2 | R2 | Q3 | R3 |
I would like to read a row and copy the row into another table with columns(ID is the foreign key) --
ID | Q_ID | R |
Ultimately, One row in the first table becomes three rows in the table 2.
Can anyone give me any pointers...
Thanks,
|
|
|
|
|
Re: INSERT query [message #403838 is a reply to message #403799] |
Tue, 19 May 2009 02:41 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Or you can write a Union based query to return the data in the way that you want:
INSERT INTO <table>
SELECT id,q1,r1 from <table>
union all
SELECT id,q2,r2 from <table>
...
|
|
|
Re: INSERT query [message #403867 is a reply to message #403799] |
Tue, 19 May 2009 05:10 |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
If you are using Oracle 9i and UP then use multitable insert:
CREATE TABLE MCK00 ( ID NUMBER, Q1 NUMBER, R1 NUMBER, Q2 NUMBER, R2 NUMBER, Q3 NUMBER, R3 NUMBER );
CREATE TABLE MCK01 ( ID NUMBER, Q NUMBER, R NUMBER);
INSERT INTO MCK00 VALUES ( 1,1,1,2,2,3,3);
COMMIT;
INSERT ALL
WHEN Q1 IS NOT NULL THEN
INTO MCK01 (ID, Q, R) VALUES ( ID, Q1, R1 )
WHEN Q2 IS NOT NULL THEN
INTO MCK01 (ID, Q, R) VALUES ( ID, Q2, R2 )
WHEN Q3 IS NOT NULL THEN
INTO MCK01 (ID, Q, R) VALUES ( ID, Q3, R3 )
SELECT * FROM MCK00;
COMMIT;
SELECT * FROM MCK01
HTH.
|
|
|
Re: INSERT query [message #403873 is a reply to message #403867] |
Tue, 19 May 2009 05:33 |
|
Michel Cadot
Messages: 68712 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote: | When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.
|
I posted clue, your solution is rude and direspectful towards what I posted, it just means that is worth nothing.
It is disrecpect towards OP, it just means he is unable to find the answer from the clue I posted.
Regards
Michel
|
|
|
Goto Forum:
Current Time: Tue Nov 12 23:38:11 CST 2024
|