Let's say I have this table:
|TeamId |Name |ActiveYear |ParentTeamId |OriginTeamId |
+-------+---------------+-----------+-------------+-------------+
|1 |Founding Team |2020 |NULL |NULL |
|2 |Elders Team |2020 |1 |NULL |
|3 |Younger Team |2020 |1 |NULL |
|4 |Women Team |2020 |2 |NULL |
|5 |Men´s Team |2020 |2 |NULL |
When I query it, I can see the hierarchy:
WITH abcd AS
(
-- anchor
SELECT
CAST((Convert(varchar(20), TeamId)) AS VARCHAR(1000)) AS "Tree",
TeamId, [Name], Activeyear, ParentTeamId, OriginTeamId
FROM
Teams AS p
WHERE
ParentTeamId IS NULL And ActiveYear = 2020
UNION ALL
--recursive member
SELECT
CAST((CONVERT(varchar(20), a.tree) + '/' + CONVERT(varchar(20), c.TeamId)) AS VARCHAR(1000)) AS "Tree",
c.TeamId, c.[Name], c.Activeyear, c.ParentTeamId, c.OriginTeamId
FROM
Teams AS c
JOIN
abcd AS a ON c.ParentTeamId = a.TeamId
WHERE
c.ActiveYear = 2020
)
SELECT *
FROM abcd
Now, I need to update this table, recreating the same structure but with new id's and the Activeyear = 2021.
So, the new parent record will still be the parent of the other new records.
I want to make a query, which permit me to "clone" this data and insert it with some updates: ActiveYear = 2021, OriginTeamId(OriginTeamId is to identify where that info came from)
How can I do that?
One way to do this is in two steps. Insert the rows without the ids. Then figure out the ids:
insert into teams (name, activeyear, parentid)
select name, 2021, parentid
from teams
where actdiveyear = 2020;
Now update the values by looking up the corresponding ids. Window functions can help here:
insert into teams (name, activeyear, ParentTeamId)
select name, 2021, ParentTeamId
from teams
where activeyear = 2020;
with corresponding as (
select t2020.teamid as teamid_2020, t2021.teamid as teamid_2021
from teams t2020 join
teams t2021
on t2020.name = t2021.name
where t2020.activeyear = 2020 and t2021.activeyear = 2021
)
update t
set ParentTeamId = c.teamid_2021
from teams t join
corresponding c
on t.ParentTeamId = c.teamid_2020
where t.activeyear = 2021;
Here is a db<>fiddle.