Database re-structuring


#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?


#2

On 4/19/06, Dick D. removed_email_address@domain.invalid 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.

Ryan


#3

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

zdennis wrote:

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
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFERv7KMyx0fW1d8G0RAuihAJ9BZQ+thrHF7ePEOboIrxiJum9mxgCfZPAP
QJ/10EUzkWvbmI676bp7IUI=
=ETBE
-----END PGP SIGNATURE-----


#4

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ryan L. wrote:

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

Zach
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFERv3pMyx0fW1d8G0RAmNbAJwOaBKouHWL0xDJh5WjmG0uKnj1IgCfb+RM
B5NCP6dClBplgDzh+LIQhW4=
=L1Pr
-----END PGP SIGNATURE-----


#5

Robert K. 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


#6

2006/4/19, Dick D. removed_email_address@domain.invalid:

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?

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