CODEWITHSUNDEEP
databaseoracle-databaseplsql
GrayR
GrayR

Reputation: 1385

Oracle, cloning rows in tables based on id

I have a bunch of tables with a foreign key to some main table in Oracle DB. In each of this tables there are several rows for given id value from main table.

My task is for given id from main table duplicate all records referencing that id in other tables but with new id value as a reference to main table.

I already wrote PL/SQL procedure which goes over each of my tables and does what is required in a most obvious and straightforward way. But, I wonder, what is the more elegant solution to this problem? Maybe writing some general procedure for generic case and then calling it for each table from my main procedure, or maybe something even more effective can be done here?

Upvotes: 0

Views: 158

Answers (2)

GrayR
GrayR

Reputation: 1385

Following solution is the closest to what I needed and is based on answer to this question:

begin
FOR r IN (SELECT *
                  FROM table_name
                 WHERE fk_id = "old fk value")
      LOOP
         r.pk_id := pk_seq.NEXTVAL;
         r.fk_id := "new fk value";

         INSERT INTO table_name
              VALUES r ;
      END LOOP; 
end;

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231791

A solution that "goes over each table" and that operates in an "obvious and straightforward way" would seem rather likely to be the most elegant solution, at least for a large number of problems.

If you are trying to build a generic utility, you could do something like query the data dictionary to get the foreign keys that relate to a particular table and walk the tree to find the child tables, grandchild tables, etc. and dynamically build the logic you want. That's going to involve a bunch of dynamic SQL, though, which means that it will take longer to write the code, it will be harder to read and modify in the future, it will probably be slower, and it will be more fragile if you ever want to have different logic for different tables (for example, if you want to treat history or audit tables differently) or if you want to handle things that are related but not enforced by a foreign key. These may be acceptable trade-offs if you want to have similar logic that works for many different base tables or if you're building an application that you want to work with arbitrary databases. They're probably not acceptable trade-offs if you're just trying to do something that will work with one or two base tables in a custom-built system.

Upvotes: 2

Related Questions