转换一个 Oracle 表（数据）以填充另一个略有不同的 Oracle 表的最佳方法是什么？

Question

我有两张 Oracle 表，一张旧一张，一张新的。 旧表的设计很糟糕（请注意，比我的更糟糕），但是有很多当前数据需要迁移到我创建的新表中。

新表有新列，不同的列。

我想到只编写一个 PHP 脚本或带有一大堆字符串替换的东西......显然这是一种愚蠢的方法。

我真的很希望能够在此过程中清理一下数据。有些它是用标记存储的（例如：“
名字
”），大量空格等，所以我真的很想在将其放入新表之前修复所有这些问题。

有没有人有做这样的事情的经验？我应该怎么办？

谢谢 ：）

Answer 1

我经常这样做 - 您可以使用简单的选择状态进行迁移：

create table newtable as select 
 field1,
 trim(oldfield2) as field3,
 cast(field3 as number(6)) as field4,
 (select pk from lookuptable where value = field5) as field5,
 etc,
from
 oldtable

在清理和转换数据时，使用像 php 等中间语言可以做的事情实际上很少是您在本机 SQL 中做不到的。

对于更复杂的清理，您始终可以创建一个 sql 函数来完成繁重的工作，但我已经清理了一些非常糟糕的数据，而没有诉诸于此。不要忘记在oracle中你有decode、case语句等。

Answer 2

我会检查一个 ETL 工具，例如 Pentaho Kettle。您将能够从旧表中查询数据，转换和清理数据，然后将其重新插入到新表中，所有这些都可以使用一个很好的所见即所得工具完成。

这是我之前回答的关于使用 Kettle 进行数据迁移和操作的问题。
使用 Pentaho Kettle，如何从单个表加载多个表，同时保持引用完整性？

Answer 3

如果数据量不大，并且您只打算执行一次，那么将很难击败自行启动的程序。特别是如果您有一些需要实现的自定义逻辑。
下载、学习和使用所花费的时间使用工具（例如 pentaho 等）可能不值得您花时间。

编码 select *，更新内存中的列 &使用 PHP 或任何其他编程语言可以快速完成插入操作。

话虽这么说，如果您发现自己经常这样做，那么 ETL 工具可能值得学习。

Answer 4

我自己正在从事一个类似的项目 - 将数据从包含几十个表的一个模型迁移到具有相似数量表的稍微不同的模型。

我采取了为每个目标表创建 MERGE 语句的方法。源查询获取所需的所有数据，根据需要对其进行格式化，然后如果该行已存在则进行合并，并根据需要更新/插入。这样，我可以在开发解决方案时多次运行该语句。

Answer 5

取决于转换过程的复杂程度。如果可以很容易地用单个 SQL 语句来表达，那么您就已经准备好了；只需创建 SELECT 语句，然后执行 CREATE TABLE / INSERT 语句。但是，如果您需要执行一些复杂的转换或（令人震惊）拆分或合并任何行以正确转换它们，则应该使用管道表函数。但听起来情况并非如此。尝试坚持上面另一个克里斯建议的单一陈述。您绝对不想将数据从数据库中取出来进行转换，因为传入和传出 Oracle 的速度始终比将数据全部保留在数据库中要慢。

还有一些提示：

• 如果表已存在并且您正在执行 INSERT...SELECT 语句，请在插入时使用 /*+ APPEND */ 提示，以便执行批量操作。请注意，CREATE TABLE 默认情况下会执行此操作（只要可能即可；在某些条件下无法执行批量操作，例如，如果新表是索引组织表、具有触发器等。
• 如果您使用的是 10.2 或更高版本，则还应该考虑使用 LOG ERRORS INTO 子句将拒绝的记录记录到这样，如果一条记录出现意外错误，您就不会丢失整个操作。