INSERT TB1 SELECT B.ID,B.TITLE,A.ID FROM TB1 A,TB2 B WHERE B.TITLE LIKE '%'+A.TITLE+'%' ?
declare @title varchar(20) set @title='XXX' insert into table1(pid,title) select (select ID from table1 where title=@title) as pid,title from 子表 where exists(select 1 from table1 where CHARINDEX(@title,子表.title)>0)
DECLARE @cMaster TABLE ( id int, pid int, title varchar(10)); INSERT INTO @cMaster --准备主表数据 select 1, null, 'sg' union all select 2, null, 'f'
;WITH cChild(id, title) as --准备子表数据 ( select 1, 'sg1' union all select 2, 'sg2' ), c_result as ( SELECT b.* FROM @cMaster m CROSS APPLY (SELECT c.id, m.id pid, title FROM cChild c WHERE CHARINDEX(m.title, c.title) <> 0) b ) INSERT INTO @cMaster SELECT * FROM c_resultSELECT * FROM @cMaster ORDER BY title--测试结果: /* id pid title ----------- ----------- ---------- 2 NULL f 1 NULL sg 1 1 sg1 2 1 sg2(4 行受影响) */造了几条数据,不知能否满足你的要求?
set @title='XXX'
insert into table1(pid,title)
select (select ID from table1 where title=@title) as pid,title from 子表 where exists(select 1 from table1 where CHARINDEX(@title,子表.title)>0)
(
id int,
pid int,
title varchar(10));
INSERT INTO @cMaster --准备主表数据
select 1, null, 'sg' union all
select 2, null, 'f'
;WITH cChild(id, title) as --准备子表数据
(
select 1, 'sg1' union all
select 2, 'sg2'
),
c_result as
(
SELECT b.*
FROM @cMaster m
CROSS APPLY
(SELECT c.id,
m.id pid,
title
FROM cChild c
WHERE CHARINDEX(m.title, c.title) <> 0) b
)
INSERT INTO @cMaster
SELECT *
FROM c_resultSELECT *
FROM @cMaster
ORDER BY title--测试结果:
/*
id pid title
----------- ----------- ----------
2 NULL f
1 NULL sg
1 1 sg1
2 1 sg2(4 行受影响)
*/造了几条数据,不知能否满足你的要求?