Geek Answers Handbook

Migrate MySQL to a table with a different structure

My company is currently moving our databases, moving one set of tables from an old instance of MySQL to a new one. We did some development before this transfer, and the structure of some tables was changed from the original (for example, the columns were deleted).

So, currently I dumped the data from the old database and am now trying to insert it into a new table. Of course, imported borks when he tries to insert rows with more fields than the table.

What is the best way (preferably a scenario, because I foresee that I need to do this a few more times) to import only the fields I need into a new table?

+5
source share
4 answers

Update the following:

SELECT 'INSERT INTO NEW_TABLE ... ('+ to.column +');'
  FROM OLD_TABLE ot

You need an INSERT statement for a table in a new database with a list of columns. Then fill in part of the value accordingly based on the values ​​in the old table. Run in the old environment and you will have your own inserts with data for the new environment - just copy the "paste" into the script.

Remember that the corresponding data types must be processed accordingly - dates (including time), and strings must be processed, because you are dealing with text.

+2
source

First of all, create a new database with the old structure or temporary tables in the current database. Then run the script with insert instructions for each row, but only those fields that are in the new structure should be in the values.

insert into newTable select row1,row2 from tempTable
+2

, infile:

- Dump datas

SELECT * INTO OUTFILE 'mybigtable.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM mybigtable

-

LOAD DATA LOCAL INFILE 'mybigtable.csv' 
  INTO TABLE mynewbigtable
  FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'  
  (@col1,@col2,@col3,@col4) set name=@col4,id=@col2;

:

http://dev.mysql.com/doc/refman/5.6/en/insert-speed.html

http://dev.mysql.com/doc/refman/5.6/en/load-data.html

+1
source

If you are using MySQL 5.1, a powerful, although perhaps overkill, case, the solution is to make xml mysqldump and use XSLT to convert it. Unfortunately, re-importing this XML file is not supported in 5.0, you will need 5.1, 5.4 or 6.0

0
source

More articles:


All Articles