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.