Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    6

    Unanswered: Insert record from old table in two new tables

    Take this for example:

    I have a old table with movies. And now I want split this table in a movie table and a genre table.
    In the old table I have a column with genres. I want to move them to another table genres. And store the id in the movie table and not the name of the genre.

    So I need a sql query that goes trough my old table record by record. Store all the fields without the genre field in my new movie table. And add the genre name from my old table in the genre table and give the auto generate primary key to the new movie table. If the genre already exist in the genre table I don't need to create the genre so I only have to give the corresponding id to the new movie table.

    Is that possible using sql and how?

    Im working with DB2.

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    it is possible. Can you please post the table column names in old table and desired new columns name in two new tables. It would be easier to write SQL...
    Grofaty

  3. #3
    Join Date
    Nov 2004
    Posts
    6
    I forgot something. The old table have two columns with genres. These columns always has a value.

    My tables: (don't forget these tables are not my real tables. I took this axample because the real tables are not so understandable)

    OLD TABLE

    ID, TITLE, GENRE1, GENRE2
    1,Movie1,Action, Comedy
    2, Movie2, Romantic, Comedy


    NEW TABLE
    ID, TITLE, GENRE_ID
    1, Movie1, 1
    2, Movie2, 2

    GENRES
    ID, GENRE1, GENRE2
    1, Action, Comedy
    2, Romantic, Comedy

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    try this out:

    Create tables:
    CREATE TABLE OLD_TABLE (ID INT NOT NULL PRIMARY KEY, TITLE CHAR(20), GENRE1 CHAR(20), GENRE2 CHAR(20));
    CREATE TABLE NEW_TABLE (ID INT NOT NULL PRIMARY KEY, TITLE CHAR(20), GENRE_ID INT);
    CREATE TABLE GENRES (GENRE_ID INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) , GENRE1 CHAR(20), GENRE2 CHAR(20));

    Fill in sample data to old_table:
    INSERT INTO OLD_TABLE VALUES (1, 'Movie1', 'Action', 'Comedy');
    INSERT INTO OLD_TABLE VALUES (2, 'Movie2', 'Romantic', 'Comedy');

    Insert/update new_table and genres table:
    INSERT INTO NEW_TABLE SELECT ID, TITLE, CAST (NULL AS INT) FROM OLD_TABLE;
    INSERT INTO GENRES (GENRE1, GENRE2) SELECT GENRE1, GENRE2 FROM OLD_TABLE;
    UPDATE NEW_TABLE X SET X.GENRE_ID = (SELECT A.GENRE_ID FROM GENRES A, OLD_TABLE B, NEW_TABLE C WHERE A.GENRE1=B.GENRE1 AND B.ID=C.ID AND X.ID=B.ID) WHERE X.GENRE_ID IS NULL;

    Tip: After each statement I suggest you run following three statements:
    select * from old_table;
    select * from new_table;
    select * from genres;
    to see what has each SQL changed.

    Hope this helps,
    Grofaty
    Last edited by grofaty; 10-30-07 at 09:17.

  5. #5
    Join Date
    Nov 2004
    Posts
    6
    I can't try it now. So I have to test it later today. But I think that the second insert doesn't check if the combination of genre1 and genre2 exist.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The second insert should be:

    INSERT INTO GENRES (GENRE1, GENRE2) SELECT distinct GENRE1, GENRE2 FROM OLD_TABLE;


    The update is not quite right either:
    UPDATE NEW_TABLE X SET X.GENRE_ID = (SELECT A.GENRE_ID FROM GENRES A, OLD_TABLE B, NEW_TABLE C WHERE A.GENRE1=B.GENRE1 and a.genre1 = b.genre2 AND B.ID=C.ID AND X.ID=B.ID) WHERE X.GENRE_ID IS NULL;


    Andy

Posting Permissions

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