How to insert into Table B, all rows from Table B, but with one column changed-CodePudding

I have a table that I'm trying to duplicate every row of, but with one column having a different value. The problem is getting that value involves another table which has no current relation.

Table Search:

SearchID	Name
1	A 1
2	B 1
3	C 1
4	A 2
5	B 2
6	C 2

Table SearchFields:

SearchFieldID	SearchID	Foo
1	1	bob
2	1	mary
3	2	tim
4	2	justin
5	3	jay
6	3	anthony

What I'm trying to get is Table SearchFields to look like this:

Table Expected SearchFields:

SearchFieldID	SearchID	Foo
1	1	bob
2	1	mary
3	2	tim
4	2	justin
5	3	jay
6	3	anthony
7	4	bob
8	4	mary
9	5	tim
10	5	justin
11	6	jay
12	6	anthony

The only relation I can see is part of the Name column on Search (everything up to the last space gives me duplicate rows, and I can take the higher SearchId from each duplicate to give me the new SearchId to use)

So far I've been able to do things like:

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)))

This gives me

SearchID	Name
4	A
5	B
6	C

But I'm not sure how I can take those results and map them to SearchFields, to do something like:

;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)))
)
-- this part obviously wrong, not sure how to connect the two
insert into SearchFields(SearchId, Foo)
select c.SearchId, sf.Foo from cte c
union all
select Foo from SearchFields sf

CodePudding user response：

Think I figured it out.

insert into searchfields(searchid, Foo)
select 
(
select top 1 searchid from search where searchid = 
    (select top 1 max(s.searchid) 
     from search s 
     group by 
     substring(name, 1, len(name) - charindex(' ', reverse(name))) 
     having sf.searchid = min(s.searchid))
) [SearchId], Foo
from searchfields sf
order by searchid