Home » SQL & PL/SQL » SQL & PL/SQL » INSERT query
INSERT query [message #403799] Mon, 18 May 2009 15:34 Go to next message
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 #403800 is a reply to message #403799] Mon, 18 May 2009 15:53 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Use three inserts
Re: INSERT query [message #403816 is a reply to message #403799] Mon, 18 May 2009 23:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Have a look at INSERT ALL statement, if your version knows it.

Regards
Michel
Re: INSERT query [message #403838 is a reply to message #403799] Tue, 19 May 2009 02:41 Go to previous messageGo to next message
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 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 Go to previous message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: SMS using PL/SQL(merged)
Next Topic: Help about SQL Query
Goto Forum:
  


Current Time: Tue Dec 06 04:50:04 CST 2016

Total time taken to generate the page: 0.08922 seconds