Re: [SQL] How to merge two tables into one ?

From: Lothar Armbrüster <lothar.armbruester_at_rheingau.netsurf.de>
Date: 08 Jul 98 20:13:15 +0100
Message-ID: <1272.493T785T12133592@rheingau.netsurf.de>

On 08-Jul-98 13:57:00 Zahi Al-Jamous wrote:

>Hi all,

>i have two tables structured like this :

>Table policy
> Id
> Number
> Customer
> Adress
>Table Commentary
> Id (foreign key references policy.Id)
> LineNumber
> Comment

>I want to have one table :
>table PolicyNew
> Id
> Number
> Customer
> Adress
> Comment

>Where "Comment" is the concatenation of all the "Comment" fields in
>table Commentary where Policy.Id = Commentary.Id

>Can someone help me writing this script ?

I would try something like this:

insert into policynew

   (id,number,customer,adress)
(select id,number,customer,adress from policy);

/* Now some PL/SQL to merge the comments */ declare

cursor comm_cur is

   select

      id,comment
   from

      commentary
   order by id,linenumber;

begin

   for comm_rec in comm_cur loop

      update policynew
         set comment=comment||comm_rec.comment||chr(10)
      where
         id=comm_rec.id;

   end loop;
end;
/

This is quite straight forward but it should work. You eventually have to change the chr(10) to your operating system's line separator.

Hope that helps,
Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |

Received on Wed Jul 08 1998 - 14:13:15 CDT