Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2010
    Posts
    2

    Unanswered: Transfering data beetween tables

    Hey,
    I would like transfer data from one tabel A to B, the tables has identical structure.
    TABLE A/B
    (id_wydarzenia VARCHAR2(10) CONSTRAINT wy_id_wydarzenia_NN NOT NULL,
    nazwa_dysycpliny VARCHAR2(15),
    nr_wydarzenia CHAR(9) CONSTRAINT wy_nr_wydarzenia_NN NOT NULL,
    gospodarz VARCHAR2(20),
    gosc VARCHAR2(20),
    data_roz DATE,
    rozgrywki VARCHAR2(20),
    kurs0 NUMBER(5,2),
    kurs1 NUMBEr(5,2),
    kurs2 NUMBER(5,2),
    roz CHAR(3),
    rezultat VARCHAR2(7),
    wynik VARCHAR2(1),
    CONSTRAINT wy_id_wydarzenia_pk PRIMARY KEY(id_wydarzenia))

    I need transfer only that records from table A into B which doesnt exist in B (identification is by field called nr_wydarzenia). I created new block, and used WHEN-NEW-BLOCK instance trigger:

    DECLARE
    cursor pobierz_zewnetrzne is Select * from Wydarzenia@zew;
    cursor sprawdz_czy_istnieje(numer in varchar2) is select roz from Wydarzenia where nr_wydarzenia=numer;
    tmp varchar2(9);
    begin

    for rekord in pobierz_zewnetrzne LOOP
    open sprawdz_czy_istnieje(rekord.nr_wydarzenia);
    fetch sprawdz_czy_istnieje into tmp;
    close sprawdz_czy_istnieje;

    if (tmp is null) then
    select id_wydarzenia.nextval into ODAJ_WYDARZENIA.ID_WYDARZENIA from dual;
    ODAJ_WYDARZENIA.NAZWA_DYSCYPLINY:=rekord.NAZWA_DYS CYPLINY;
    ODAJ_WYDARZENIA.NR_WYDARZENIA:=rekord.NR_WYDARZENI A;
    ODAJ_WYDARZENIA.GOSPODARZ:=rekord.GOSPODARZ;
    ODAJ_WYDARZENIA.GOSC:=rekord.GOSC;
    ...
    commit_form;


    end if;
    end loop;
    end;

    This isn`t working, need your help guys

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    That's too complicated. Why not simply
    Code:
    insert into table_b
      select * from table_a
      minus
      select * from table_b
    You'll probably want to add some WHERE condition(s), but that's the general idea.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    INSERT INTO table_b
    SELECT *
    FROM   table_a a
    WHERE  a.nr_wydarzenia IN (SELECT nr_wydarzenia
                               FROM   table_a
                               MINUS
                               SELECT nr_wydarzenia
                               FROM   table_b)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •