You may have known that we can insert data into a table using data from select query in MySQL. Let's assume we have two tables. First one is TableA and the other is TableB. TableA has 10 fields, one of them is First Name field. TableB has two fields: id (as primary key) and name. Then I import all datas in First Name field into name field by using this query syntax.
insert into TableB (name) select `First Name` from TableA;
Now all datas in First Name field in TableA has been imported into name field in TableB. Unfortunately when I check all of those imported datas, the primary key field in TableB (id field) has random value, it doesn't have sequential value as usual. Although I have set it for auto increment. In other words, the primary key value in TableB is mess up. How to solve this problem?
After doing some research and find nothing , I try to modify my query above. I supposed that may be I have to specify the order of data before they're inserted into TableB. And luckily, this way works. This is it.
insert into TableB (name) select `First Name` from TableA order by `First Name`;
Please notice that I give backtick for First Name field. This is a way to do query for a field which its name contains space. Please read my post about Select Column Name With Space for more details.