sql : duplicate row and all related rows

Question

i have the following 3 related tables

Schools           Departments          Classes
---------------   ------------------   -----------------
ID                ID                   ID
School_Name       ID_Schools           ID_Departments
                  Department_Name      Class_Name

and a little sample about what they contain ( I'll draw the best i can )

---- Schools ---
ID   School_Name
----------------
 1   School_1  <----------------------\
 2   School_2                         |
 n   ........                         |
                                      |
---- Departments ----------------     |
ID   ID_Schools   Department_Name     |
---------------------------------     |
 1            1   Dept_1  <-----------/ -->--\
 2            1   Dept_2  <-----------/ -->--|------\
 3            2   Dept_1                     |      |
 n           ..   ......                     |      |
                                             |      |
---- Classes -------------------             |      |
ID   ID_Departments   Class_Name             |      |
--------------------------------             |      |
 1                1   Class_1  <-------------/      |
 2                1   Class_2  <-------------/      |
 3                1   Class_3  <-------------/      |
 4                2   Class_1  <--------------------/
 n               ..   .......

all IDs are auto-increment

i'm looking for a way to copy 'School_1' hierarchy. and the problem is how can i maintain relationship between new rows ?

for example to copy 'School_1' hierarchy, I'll insert a new raw into Schools table, which eventually will produce a new ID ( 5 for example ).

---- Schools ---
ID   School_Name
----------------
 5   School_1

departments 'Dept_1' and 'Dept_2' that belong to 'School_1' will get new IDs

---- Departments ----------------
ID   ID_Schools   Department_Name
---------------------------------
16            5   Dept_1
17            5   Dept_2

and classes will also get new IDs

---- Classes -------------------
ID   ID_Departments   Class_Name
--------------------------------
56               16   Class_1
57               16   Class_2
58               16   Class_3
59               17   Class_1

how can i achieve this a simple and smart way ?

Can not answer this right now, but voted question up for its perfect form. I wish all SO questions were as clear and understandable as this one! — d.sergeiev, Jul 24 '14 at 11:45

score 0 · Answer 1 · edited May 23 '17 at 12:21

I'd advice to use SQL INSERT AS SELECT magic, like described here: Insert into ... values ( SELECT ... FROM ... ).

Let you old School ID be 7 and your new School ID be 17, then you can create a query for each table as INSERT INTO Departments(id, id_schools, name) VALUES (NULL, 17, (SELECT name FROM Departments WHERE id_schools = 17)).

It gets a bit tricky when you need to insert Classes - by - Departments (with multiple departments), but you can either manually fill in WHERE id_departments IN (5,6,13, etc) or automate it as WHERE id_departments IN (SELECT id FROM Departments WHERE School_ID = 17)

P.S.: This is more like advice then an answer, so some details like SQL dialect or code quality may be not appropriate, but i am sure about the approach itself.

jded · Answer 2 · 2014-07-24T12:42:47.260

I tried to write a simple Stored Procedure for this purpose. I don't have the time to test the logic of it, but I think it can give my way of thinking for this problem. Test it out...

CREATE PROCEDURE [dbo].[copySchoolHierarchy] (@SchoolId As Int) 
As Begin

BEGIN TRANSACTION

BEGIN TRY

    DECLARE @tmpSchoolName NVARCHAR(100),
            @tmpNewSchoolID INT

    --First insert into schools
    SELECT @tmpSchoolName = School_Name FROM Schools WHERE ID = @SchoolId 
    INSERT INTO SCHOOLS (School_Name) VALUES (@tmpSchoolName)
    SELECT @tmpNewSchoolID = SCOPE_IDENTITY()

    --Then get the departments
    INSERT INTO Departments (ID_Schools, Department_Name)
    SELECT @tmpNewSchoolID AS someId, Department_Name FROM DEPARTMENTS Where ID_Schools = @SchoolId

    --and the classes
    INSERT INTO Classes (ID_Departments, Class_Name)
    SELECT C.ID_Departments, C.Class_Name FROM Classes C 
    INNER JOIN DEPARTMENTS D ON C.ID_Departments = D.ID
    WHERE D.ID_Schools = @tmpNewSchoolID 

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    --Raise some exception here...

    ROLLBACK TRANSACTION
END CATCH
END

score 0 · Answer 3 · answered Jul 24 '14 at 12:59

It's easy to copy Schools and Departments. To copy Classes we need a function which converts old department ID into new one based on old and new school.id values.

In case combination of Dept_id and Dept_name is unique the following approach is possible:

DECLARE @old_id int; // set it 

DECLARE @school_id int;
DECLARE @school_name varchar(100); 

SET @school_name = (SELECT school_name from schools where id = @old_id);

INSERT into schools (school_name)
  OUTPUT Inserted.id into @school_id
  VALUES (@school_name);

INSERT into departments (id_schools, department_name)
  SELECT @school_id, department_name 
    FROM departments 
      WHERE id_schools = @old_id;

INSERT into classes (ID_Departments, Class_Name)
  SELECT
    (SELECT s.ID_Departments from departments s
       WHERE s.id_schools = @school_id and s.department_name = d.department_name),
    c.class_name
  FROM classes c, departments d
    WHERE c.ID_Departments = d.ID and d.ID_Schools = @old_id;

In case (id_schools, department_name) pair is not unique you can store department_id value in the department_name field temporary and update it later (after classes creation).

sql : duplicate row and all related rows

3 Answers3