Database re-structuring

ruby-talk
1

Hi everyone, I am looking for some suggestions here.

I've got an older mysql database, one table, dumped into a SQL file.

I want to use this data in my new database, only this time I want the
data to go into multiple tables instead of one big table.

I was thinking of writing a script to parse the SQL file and based on
each entry, put the information where it needs to go. I also thought of
feeding the SQL file into my new database, and write a script that went
thought each row in the data of old table (with a SELECT *) and figured
out where to put it in the new tables.

Is this an insane way to do things? I have a feeling I am doing a lot
more work than necessary. Does anyone have any suggestions?

···

--
Posted via http://www.ruby-forum.com/.

2

If the dumped SQL is fairly simple and regular, I'd just write a
script to parse it and add things to the right tables. I think it is
overkill to import it into a new table just so you can later select
and re-insert it.

Ryan

···

On 4/19/06, Dick Dishkuvek <dishkuvek@0xf00d.com> wrote:

Is this an insane way to do things? I have a feeling I am doing a lot
more work than necessary. Does anyone have any suggestions?

3

Since mysql 5 you can also write a stored procedure that will read the
table with a cursor and distribute the data to several tables.
Disclaimer: haven't used mysql stored procedures yet so I don't know
how mature they are.

Kind regards

robert

···

2006/4/19, Dick Dishkuvek <dishkuvek@0xf00d.com>:

Hi everyone, I am looking for some suggestions here.

I've got an older mysql database, one table, dumped into a SQL file.

I want to use this data in my new database, only this time I want the
data to go into multiple tables instead of one big table.

Is this an insane way to do things? I have a feeling I am doing a lot
more work than necessary. Does anyone have any suggestions?

--
Have a look: Robert K. | Flickr

4

Ryan Leavengood wrote:

···

On 4/19/06, Dick Dishkuvek <dishkuvek@0xf00d.com> wrote:

Is this an insane way to do things? I have a feeling I am doing a lot
more work than necessary. Does anyone have any suggestions?

If the dumped SQL is fairly simple and regular, I'd just write a
script to parse it and add things to the right tables. I think it is
overkill to import it into a new table just so you can later select
and re-insert it.

Depending on how big the table was, you could do this all from within a
mysql batch script, by using "insert ... select" statements.

Zach

5

Robert Klemme wrote:

Since mysql 5 you can also write a stored procedure that will read the
table with a cursor and distribute the data to several tables.
Disclaimer: haven't used mysql stored procedures yet so I don't know
how mature they are.

Kind regards

robert

Thanks for the suggestion Robert (and everyone else). I will have a
look at these "stored procedures" on mysql.org

···

--
Posted via http://www.ruby-forum.com/\.

6

zdennis wrote:

Ryan Leavengood wrote:

Is this an insane way to do things? I have a feeling I am doing a lot
more work than necessary. Does anyone have any suggestions?

If the dumped SQL is fairly simple and regular, I'd just write a
script to parse it and add things to the right tables. I think it is
overkill to import it into a new table just so you can later select
and re-insert it.

Depending on how big the table was, you could do this all from within a
mysql batch script, by using "insert ... select" statements.

Actually it has nothing to do with big... it just has to do with writing
a mysql batch script...

Zach

···

On 4/19/06, Dick Dishkuvek <dishkuvek@0xf00d.com> wrote: