Insert record from old table in two new tables

[Davidnyh]

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.

 

[mikej336]

I have not used db2 but I can give you some general ideas.

1st. create a query to select distinct genres into a new table. Change the structure on the new table to add the auto generate primary key.

2nd change the structure on the movie table to add a field for your numerical genre (probably interger).

3 create a query to update new numercal genre based on what is in the old genre field and a join with the new genre table.

4. Once it is correct, the old text genre field from the old table.

Although I cannot think of how this will help you other then save you a little disk space.

Mike

 

[mikej336]

oops...

4. Once it is correct, delete the old text genre field from the old table.

Mike