| Running the In-Place Conversion | KEVIN RESCHENBERG
05-09-2005 |
Today let's wrap up the topic of in-place conversions. By now we
have developed our final list of tables and fields containing
the valeus to be converted (department IDs, for example). We've
eliminated the empty tables and have reviewed the list. We also
have a mapping of "from" and "to" values, maybe in a spreadsheet.
Before proceeding, it's important to determine whether the mapping
is 1-to-1, many-to-1 or 1-to-many. Let's take the last case first.
If you have been given a spreadsheet with more than one line per
"old" department value, you have a problem. Maybe it's a typo on
the spreadsheet, or maybe you need additional information to determine
when department A is to be changed to department 1 and when it is to
be changed to department 2. Assuming this is not the case, let's
continue.
A 1-to-1 mapping is the easiest one to handle: Department A becomes
department 1. If you have a many-to-1 mapping (both departments A
and B become new department 1), there is a situation you must check.
What if this field is a key on one or more tables? In that case,
you could be creating duplicate keys. I've run into this situation.
The solution was to combine the rows in a logical way—for example,
by keeping one of the generated rows, or by adding numeric values
on the two rows and dropping one of them, or by keeping one row
and handling the other one manually.
Now to the actual conversion. First we must load our mapping
spreadsheet into a temporary table having at least two columns—the
"old" value and the "new" translated value. (Of course, include
SETID if necessary.) It would
be a good idea at this point to join the temporary mapping table
with the main "prompt" table (DEPT_TBL, for example) to be sure
that there are no missing values.
Then do your backup (of course!) annd generate UPDATE scripts.
These scripts can be generated and
executed dynamically using SQR. Or you could generate the scripts,
save them in files, and execute them directly. In the past I've
used the first method—my SQR program generated and then executed
each script. However, I've found that there are fewer tables to
be converted than might be expected. In the future, I might go the
other route and just have the scripts created in advance.
What do these scripts look like? For Microsoft SQL Server, they
follow this pattern:
UPDATE PS_MY_TABLE
SET A.DEPTID = X.NEW_VALUE
FROM PS_MY_TABLE A
, TEMP_CROSSREF_TBL X
WHERE X.OLD_VALUE = A.DEPTID
For Oracle it's a little more complicated:
UPDATE PS_MY_TABLE
SET DEPTID =
(SELECT NEW_VALUE
FROM TEMP_CROSSREF_TBL
WHERE OLD_VALUE = PS_MY_TABLE.DEPTID
)
WHERE EXISTS
(SELECT NEW_VALUE
FROM TEMP_CROSSREF_TBL
WHERE OLD_VALUE = PS_MY_TABLE.DEPTID
)
Verify that all values were converted.
An alternative
to the UPDATE method
is to generate INSERTs into a temporary table and copy all
data to that table, converting fields as necessary, and then
copy everything back. I've used that method when
there is a possibility of duplicate keys or other problems.
Either way, your program could check the results before
doing a COMMIT or ROLLBACK.
|
