How to Migrate Data from one Field to Database Table in MySQL

I have an scenario where the user wanted to migrate data from one field to a database table. This can be accomplish very easy by running a INSERT by SELECT statement. In DocuWare we have what we called KEYWORD fields, which is a separate database table for every keyword field that you may have in your file cabinet. I wanted to move the data from multiple fields from all my 60,000 documents to a KEYWORD field one by one.

Please perform a full backup of the database before you perform these changes. This a SQL script and if you don’t configure properly you can cause some serious damage to your database table.

Here is the script that will accomplish that task.

For column one:

INSERT INTO dwdata2.hr_mysql_itemslist (DWDOCID, DWKEYWORD)
SELECT DWDOCID, DOC__TYPE FROM dwdata2.hr_mysql
WHERE DOC__TYPE IS NOT NULL;

For column 2:

INSERT INTO dwdata2.hr_mysql_itemslist (DWDOCID, DWKEYWORD)
SELECT DWDOCID, CATEGORY FROM dwdata2.hr_mysql
WHERE CATEGORY IS NOT NULL;


Now, this just an example but it could apply more or less fields or larger tables.

Syntax Explanation:


INSERT INTO database-name.table-name
(destination-column1, destination-column2, destination-column1n, ...)
SELECT source-column1, source-column2, source-columnN, ...
FROM source-database-name.source-table-name
WHERE source-columnN-name IS NOT NULL;


In English:

I am inserting into a table by performing a select statement. The source columns and destination column has to be the same number of columns else it will not work.  I am also including a filter (WHERE clause) to not include the fields that are empty.