多对多无中间表转换成多对多有中间表解决方法

多对多无中间表转换成多对多有中间表
假设一个班有多个老师任课，一个老师可以教多个班
class表中有teacher字段，存储的是nvarchar类型的teacher表的一些id，比如“2,10,15”
teacher表有id和name字段
现在想增加个中间表R_TeacherClass 有teacherId和classId用于表示这种多对多关系
请问该如何向这个表中导入数据
------解决思路----------------------

--lz 参考一下拆分列值部分。 


drop table tb 拆分列值

有表tb, 如下:

id value

----------- -----------

1 aa,bb

2 aaa,bbb,ccc

欲按id,分拆value列, 分拆后结果如下:

id value

----------- --------

1 aa

1 bb

2 aaa

2 bbb

2 ccc


1. 旧的解决方法(sql server 2000)

SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b  


SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)

FROM tb A, # B

WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','


DROP TABLE #


2. 新的解决方法(sql server 2005)  


create table tb(id int,value varchar(30))

insert into tb values(1,'aa,bb')

insert into tb values(2,'aaa,bbb,ccc')

go

SELECT A.id, B.value

FROM(

  SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb

)A

OUTER APPLY(

  SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)

)B


DROP TABLE tb


/*

id value

----------- ------------------------------

1 aa

1 bb

2 aaa

2 bbb

2 ccc


(5 行受影响)

*/

------解决思路----------------------

INSERT INTO R_TeacherClass(teacherId,classId)

SELECT t1.classId,t2.teacherId FROM class t1 

INNER JOIN teacher t2 ON CHARINDEX(','+t2.teacherId+',',','+t1.teacher+',')>0

------解决思路----------------------

--确保中间表R_TeacherClass在创建前不存在

IF OBJECT_ID('R_TeacherClass')IS NOT NULL

	DROP TABLE R_TeacherClass

GO

--开始创建R_TeacherClass

SELECT

	T2.id teacherId

	,T1.id classId

	INTO R_TeacherClass

FROM

	class T1

	LEFT JOIN teacher T2 ON ','+T1.teacher+','LIKE'%,'+CAST(T2.id AS VARCHAR(10))+',%'

--查看结果

SELECT * FROM R_TeacherClass