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?

I’m working with DB2.

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

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

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

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.

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