SQL Server Duplicate records in same tables ( base - child and grandchild) Example

create.sql

-- Create College table
CREATE TABLE College (
    CollegeID INT PRIMARY KEY IDENTITY(1,1),
    CollegeName NVARCHAR(100) NOT NULL
);

-- Create Department table
CREATE TABLE Department (
    DepartmentID INT PRIMARY KEY IDENTITY(1,1),
    DepartmentName NVARCHAR(100) NOT NULL,
    CollegeID INT,
    FOREIGN KEY (CollegeID) REFERENCES College(CollegeID)
);

-- Create Student table
CREATE TABLE Student (
    StudentID INT PRIMARY KEY IDENTITY(1,1),
    StudentName NVARCHAR(100) NOT NULL,
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID)
);

-- Insert data into College table
INSERT INTO College (CollegeName)
VALUES ('Engineering College');

-- Insert data into Department table
INSERT INTO Department (DepartmentName, CollegeID)
VALUES ('Computer Science', 1),
       ('Electrical Engineering', 1);

-- Insert data into Student table
INSERT INTO Student (StudentName, DepartmentID)
VALUES ('Alice', 1),
       ('Bob', 1),
       ('Charlie', 2),
       ('David', 2),
       ('Eve', 1);

-- Verify the inserted data
SELECT * FROM College;
SELECT * FROM Department;
SELECT * FROM Student;

Duplicate.sql

-- Step 1: Duplicate the college with a new name
DECLARE @NewCollegeID INT;

-- Insert new college and capture the new CollegeID
INSERT INTO College (CollegeName)
SELECT CollegeName + ' Copy 3' 
FROM College 
WHERE CollegeID = 1; -- Assuming CollegeID = 1 is the college to duplicate

-- Get the newly inserted CollegeID
SET @NewCollegeID = SCOPE_IDENTITY();

-- Step 2: Initialize the counter and get the total number of departments
DECLARE @Counter INT = 1;  -- Initialize the counter to 1
DECLARE @MaxCount INT;     -- To store the total count of departments
DECLARE @DeptID INT;       -- Variable to store the current DepartmentID
DECLARE @DeptName NVARCHAR(100);  -- Variable to store the department name
DECLARE @OldCollegeID INT;    -- Variable to store the old CollegeID (1 in this case)
DECLARE @NewDeptID INT;    -- Variable to store the new duplicated DepartmentID

-- Get the total number of departments from the old college (CollegeID = 1)
SELECT @MaxCount = COUNT(*) 
FROM Department
WHERE CollegeID = 1;

-- Step 3: Start the loop to iterate over all departments in the old college
WHILE @Counter <= @MaxCount
BEGIN
    -- Step 4: Get the DepartmentID, DepartmentName, and CollegeID for the current department iteration
    SELECT @DeptID = DepartmentID, 
           @DeptName = DepartmentName, 
           @OldCollegeID = CollegeID
    FROM (
        SELECT DepartmentID, DepartmentName, CollegeID, 
               ROW_NUMBER() OVER (ORDER BY DepartmentID) AS RowNum
        FROM Department
        WHERE CollegeID = 1 -- Filter for the old college
    ) AS DeptWithRowNum
    WHERE RowNum = @Counter;

    -- Step 5: Insert the duplicate department with " Copy 1" appended to the department name
    -- Declare table variable to hold new DepartmentID
    DECLARE @NewDeptTable TABLE (NewDeptID INT);

    -- Insert the duplicated department and capture the new DepartmentID
    INSERT INTO Department (DepartmentName, CollegeID)
    OUTPUT inserted.DepartmentID INTO @NewDeptTable (NewDeptID)
    SELECT @DeptName + ' Copy 3', @NewCollegeID;

    -- Fetch the new DepartmentID from the table variable
    SELECT @NewDeptID = NewDeptID FROM @NewDeptTable;

    -- Step 6: Duplicate all students from the original department and link them to the new department
    INSERT INTO Student (StudentName, DepartmentID)
    SELECT StudentName + ' Copy 3', @NewDeptID
    FROM Student
    WHERE DepartmentID = @DeptID;

    -- Step 7: Increment the counter to move to the next department
    SET @Counter = @Counter + 1;
END

-- Step 8: Verify the new departments and students
SELECT * FROM Department;
SELECT * FROM Student;