Re: multi-table insert puzzler

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sat, 10 Mar 2007 08:07:11 +0100
Message-ID: <45f2591d$0$21514$426a74cc@news.free.fr>

"foothillbiker" <foothillbiker_at_gmail.com> a écrit dans le message de news: 1173486629.255858.110960_at_j27g2000cwj.googlegroups.com...
| All,
|
| platform: 10.2.0.2 / solarisx86 64bit
|
| I am transforming data from table A to tables "Guardian" and "Ward"
|
| Table A contains:
| GuardianID
| Wards (comma delimited list of 0 or more progeny, all in a single,
| VARCHAR2(2000) field)
|
| I'd like to, in a single pass through Table A:
| - populate Guardian with the GuardianID
| - add one (1) record to Ward for each of the associated children.
|
| Ideally I would be able to
| - autogen an id for the guardian
| - use that id as an fk in thw Ward table
|
| I /think/ this is a job for INSERT ALL but am not quite sure.
|
| As ever, I appreciate your collective wisedom.
|
| REgards,
| Chas.
|

I'm quite sure you can do it with INSERT ALL. Here's a start:

SQL> VARIABLE liste VARCHAR2(100)
SQL> EXECUTE :liste := '5, 25, 41, 52';

PL/SQL procedure successfully completed.

SQL> WITH
  2 liste AS (

  3      SELECT SUBSTR(:liste,
  4                    INSTR(','||:liste||',', ',', 1, rn),
  5                    INSTR(','||:liste||',', ',', 1, rn+1)
  6                    - INSTR(','||:liste||',', ',', 1, rn) - 1) valeur
  7      FROM (SELECT ROWNUM rn FROM DUAL
  8            CONNECT BY LEVEL
  9                        <= LENGTH(:liste)
 10                           - LENGTH(REPLACE(:liste,',',''))+1)

 11 )
 12 SELECT TRIM(valeur) FROM liste;
TRIM(VALEUR)

---

5
25
41
52

4 rows selected.

Regards
Michel Cadot Received on Sat Mar 10 2007 - 01:07:11 CST