如何复制SearchFields表数据并替换关联的SearchID字段？

需要复制SearchFields表的所有行，但修改其中的SearchID列值。难点在于该值需要关联另一张无直接关系的Search表。

表结构及数据 #

Search表 #

SearchFields表 #

预期结果 #

预期的SearchFields表 #

关联逻辑 #

唯一关联是Search表的Name列中最后一个空格之前的部分（相同前缀的行视为重复组，取每组中最大的SearchID作为新的SearchID）。

已完成的查询 #

已写出以下分组查询，用于获取每组最大的SearchID及对应的前缀：

select 
    max(search.searchid) [SearchId],
    substring(  name, 
                1, 
                len(name) - charindex(' ', reverse(name))) [Name] 
from search
group by 
    substring( name, 
                1, 
                len(name) - charindex(' ', reverse(name)))

该查询返回结果：

错误的插入语句 #

尝试写出的插入语句无法正确关联两张表，需要修正：

;with cte (searchid, name)
as
(
select 
    max(search.searchid) [SearchId],
    substring(  name, 
                1, 
                len(name) - charindex(' ', reverse(name))) [Name] 
from search
group by 
    substring( name, 
                1, 
                len(name) - charindex(' ', reverse(name)))
)
-- 此处逻辑错误，不知道如何关联两张表
insert into SearchFields(SearchId, Foo)
select c.SearchId, sf.Foo from cte c
union all
select Foo from SearchFields sf

正确的实现方法 #

要完成插入，需要先将原SearchFields中的SearchID关联到Search表获取对应的前缀，再通过前缀匹配CTE中的新SearchID，最后插入这些匹配后的行：

;with cte_old_search_prefix as (
    -- 先获取原SearchID对应的Name前缀
    select 
        sf.SearchID as OldSearchID,
        sf.Foo,
        substring(s.Name, 1, len(s.Name) - charindex(' ', reverse(s.Name))) as NamePrefix
    from SearchFields sf
    join Search s on sf.SearchID = s.SearchID
),
cte_new_search_id as (
    -- 获取每组前缀对应的最大SearchID
    select 
        max(search.searchid) as NewSearchId,
        substring(name, 1, len(name) - charindex(' ', reverse(name))) as NamePrefix
    from search
    group by substring(name, 1, len(name) - charindex(' ', reverse(name)))
)
-- 关联两个CTE，插入新行
insert into SearchFields(SearchId, Foo)
select cns.NewSearchId, cos.Foo
from cte_old_search_prefix cos
join cte_new_search_id cns on cos.NamePrefix = cns.NamePrefix;

逻辑说明 #

1. cte_old_search_prefix：将原SearchFields的每一行关联到Search表，获取该行SearchID对应的Name前缀。
2. cte_new_search_id：就是你之前写出的分组查询，得到每个前缀对应的最大SearchID。
3. 最后将两个CTE通过NamePrefix关联，把原SearchFields的Foo值和对应的新SearchID插入到表中，就能得到预期的结果。

内容的提问来源于stack exchange，提问作者mariocatch