sql - Duplicating a row with all the rows below it in the hierarchy tables

Question

I do not know if my request has a name but I think it can be called "duplicating a row with all the rows below it in the hierarchy tables".

I want to create a stored procedure that can duplicate a record and all its childs and grandchilds records.

When I say "Duplicate" I main that all the record values in the columns will be copy to a new record on the same table (the copy should be without Primary key Id column because its automated) This Stored Procedure needs to get two parameters:

1. Parameter 1 - Table Name
2. Parameter 2 - Value of the Primary key Id (of that Table Name) that needs to duplicate.

Instructions of the code:

1. The value (Parameter 2) will find one record on the table (Parameter 1) and duplicate it (as I said same values for all the columns excluding the primary key of that table.
2. Then we will need to know all the table names that has a relationship with this primary key (of the parent table)
3. Then every child table will duplicate the records that have the same Id value (Parameter 2) with the new Id value (of the new parent record).

several things: In the dream scenario the code knows to go down levels without any limit (children, grandchildren, great-grandchildren ...) ,but I guess it is a very complex code that will contain some recursion code, so even a code containing up to 3-4 levels I will be happily accepted.

question from:https://stackoverflow.com/questions/65937288/duplicating-a-row-with-all-the-rows-below-it-in-the-hierarchy-tables

Answer 1

Waitting for answers