--写个函数处理吧，想不到什么方法
set nocount on;
if object_id('t1') is not null drop table t1;
create table t1(id int,title varchar(5));
if object_id('t2') is not null drop table t2;
create table t2(id int,title varchar(100),tableid varchar(100));
insert into t1 select 1,'A' union select 2,'B' 
union select 3,'C' union select 4,'D' union select 5,'E';
insert into t2 select 1,'ABC','' union select 2,'AB','' union select 3,'CD',''
if object_id('t_f') is not null drop function t_f;
go
create function dbo.t_f(@title varchar(100))
returns varchar(100)
as
begin
    declare @count int
    select @count=1
    declare @retval varchar(100);
    while @count<=len(@title)
    begin
        set @retval=isnull(@retval,'')+(select convert(varchar(10),id)+';' 
            from t1 where title=substring(@title,@count,1))
        select @count=@count+1;
    end
    return @retval
end
go
update t2 set tableid=dbo.t_f(title);
select * from t2;
/*
id          title      tableid
----------- ---------- ----------
1           ABC        1;2;3;
2           AB         1;2;
3           CD         3;4;
*/

------解决方案--------------------

SQL code
--CREATE TABLE t1
 --(Id INT,Title VARCHAR(2))
 --INSERT INTO t1
 --SELECT 1, 'A'
 --UNION ALL 
 --SELECT 2, 'B'
 --UNION ALL 
 --SELECT 3, 'C'
 --UNION ALL 
 --SELECT 4, 'D'
 --UNION ALL 
 --SELECT 5, 'E'
 
 --CREATE TABLE t2
 --(Id INT,Title VARCHAR(10), Table1Id INT )
 --INSERT INTO t2
 --SELECT 1, 'ABC', 0
 --UNION ALL 
 --SELECT 2, 'AB', 0
 --UNION ALL 
 --SELECT 3, 'CD', 0
 --SELECT ROW_NUMBER()OVER(PARTITION BY b.id ORDER BY b.id ) id,b.id bid,b.Title,b.table1id, CONVERT(VARCHAR(128),a.id) aid
 --FROM t2 b LEFT JOIN t1 a ON b.Title LIKE '%' + a.Title+ '%'
 
 
 
 ;with cte as(SELECT ROW_NUMBER()OVER(PARTITION BY b.id ORDER BY b.id ) id,b.id bid,b.Title,b.table1id, CONVERT(VARCHAR(128),a.id) aid
 FROM t2 b LEFT JOIN t1 a ON b.Title LIKE '%' + a.Title+ '%')
 ,cte1 as
 (select id, bid,Title,table1id,cast(aid as nvarchar(100))aid from cte where id=1 
 union all 
 select a.id, a.bid,a.Title,a.table1id,cast(b.aid+';'+a.aid as nvarchar(100)) from cte a join cte1 b on a.bid=b.bid and a.id=b.id+1)
 --SELECT * FROM cte1
 select bid,Title,table1id,aid INTO #t  from cte1 a where id=(select max(id) from cte  where bid=a.bid)  order by bid option (MAXRECURSION 0)
 /*
 bid         Title      table1id    aid
 ----------- ---------- ----------- ----------------------------------------------------------------
 1           ABC        0           1;2;3
 2           AB         0           1;2
 3           CD         0           3;4
 
 (3 行受影响)
 */
 
 UPDATE a
 SET a.table1id=b.table1id
 FROM t2 a INNER JOIN #t b ON a.id=b.bid

------解决方案--------------------

SQL code


DECLARE @t1 TABLE
(
ID VARCHAR(50),
Title VARCHAR(10)
);

if object_id('tempdb..#t') is not null
    drop table #t
DECLARE @t2 TABLE
(
ID VARCHAR(50),
Title VARCHAR(10),
TableId VARCHAR(10)
);
INSERT INTO @t1
    SELECT '1','A'
    UNION 
    SELECT '2','B'
    UNION 
    SELECT '3','C'
    UNION 
    SELECT '4','D'
    UNION 
    SELECT '5','E'

INSERT INTO @t2
SELECT '1','ABC','0'
UNION
SELECT '2','A