Oracle：迁移期间规范化数据

发布于 2024-08-03 01:31:04 字数 626 浏览 7 评论 0原文

我有一个包含大量重复数据的表，我想将其重构为 3 个表。

当前结构如下所示：

meeting_desc
meeting_date
topic_desc
...

current_table 中的数据如下所示：

meeting1,2/3/2009,abc
meeting1,2/3/2009,efg
meeting1,2/3/2009,xyz
meeting2,4/5/2009,aaa
meeting2,4/5/2009,bbb

我想创建一个会议表和一个主题表，其中 PK 来自序列：

MEETING: 
  id
  meeting_desc
  meeting_date

TOPIC:
  id
  meeting_id
  topic_desc

我不知道如何将数据插入到新表中。我已经尝试过了：

insert into MEETING select distinct 
  seq.nextval, meeting_desc, meeting_date from current_table

但这当然行不通。有没有一种简单的方法来标准化数据？

原文

I have a table with a lot of repeated data that I'd like to refactor as 3 tables.

Current structure looks like:

meeting_desc
meeting_date
topic_desc
...

And the data in the current_table looks like:

meeting1,2/3/2009,abc
meeting1,2/3/2009,efg
meeting1,2/3/2009,xyz
meeting2,4/5/2009,aaa
meeting2,4/5/2009,bbb

I would like to create a meeting table and a topic table, with PKs coming from a sequence:

MEETING: 
  id
  meeting_desc
  meeting_date

TOPIC:
  id
  meeting_id
  topic_desc

What I can't figure out is how to insert data into the new tables. I've tried:

insert into MEETING select distinct 
  seq.nextval, meeting_desc, meeting_date from current_table

but of course that doesn't work. Is there an easy way to normalize the data?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

溺深海 2024-08-10 01:31:04

放置在子查询中的 DISTINCT 应该可以工作：

SQL> INSERT INTO meeting
  2     SELECT seq.nextval, meeting_desc, meeting_date
  3       FROM (SELECT DISTINCT meeting_desc, meeting_date
  4               FROM current_table);

2 rows inserted

完成此操作后，您可以将这个新创建的表与旧表连接起来，以将生成的 id 与子表相关联：

SQL>   INSERT INTO topic
  2       SELECT m.id, topic_seq.NEXTVAL, ct.topic_desc
  3         FROM current_table ct
  4         JOIN meeting m ON (ct.meeting_desc = m.meeting_desc 
  5                            AND ct.meeting_date = m.meeting_date);

5 rows inserted

The DISTINCT placed in a subquery should work:

SQL> INSERT INTO meeting
  2     SELECT seq.nextval, meeting_desc, meeting_date
  3       FROM (SELECT DISTINCT meeting_desc, meeting_date
  4               FROM current_table);

2 rows inserted

Once this is done, you would join this newly created table with the old table to associate the generated ids to the children tables:

SQL>   INSERT INTO topic
  2       SELECT m.id, topic_seq.NEXTVAL, ct.topic_desc
  3         FROM current_table ct
  4         JOIN meeting m ON (ct.meeting_desc = m.meeting_desc 
  5                            AND ct.meeting_date = m.meeting_date);

5 rows inserted

回复收藏 0 原文

~没有更多了~