CODEWITHSUNDEEP
mysqlmigration
Hillcow
Hillcow

Reputation: 969

MySQL: migrate some content of one table into another table with a different structure

Here is what I would like to do: I have an old vBulletin forum with a couple thousands of threads and replies. I developed a Laravel forum application myself and would like to migrate data from the old forum into the new one, even though the structure is completely different. What is more, in my new MySQL table I use base64 id's that I manually have to program. So I cannot use pure MySQL to fill this in I guess.

Essentially I need to do something like this:

Old table ---- new table 'thread' -> 'title' 'text' -> 'body'

and so on... plus the thing with the base64 id's.

Any idea how to approach this? I didn't quite find anything useful using search, probably because I wasn't looking for the right keywords. Thanks a lot!

Upvotes: 1

Views: 26

Answers (1)

O. Jones
O. Jones

Reputation: 108696

Here's how to approach this kind of problem.

Start by doing a SELECT operation to generate a result set looking like your new table's columns. Maybe something like this will work for you.

 SELECT thread AS title,
        text AS body,
        TO_BASE64(SHA2(UUID(), 224)) AS base64id
   FROM old_table

(I guessed about what belongs in your base64id column. TO_BASE64(SHA2(UUID(), 224)) generates hard-to-guess pseudorandom values. You can use anything here.)

Once you're satisfied with your resultset, you can do

 INSERT INTO new_table (title, body, base64id)
 SELECT  .... your select query

to put rows into the new table.

Upvotes: 1

Related Questions