--示例--示例数据 create table tA(B_CODE varchar(50),CODE varchar(10)) insert tA select 'A,B,C,','NEWCODE' union all select 'A,' ,'NEWCODE2' union all select 'A,B,' ,'NEWCODE3'create table tB(CODE varchar(10)) insert tB select 'A' union all select 'B' union all select 'C' union all select 'D' go--处理--为表A添加ID字段 alter table tA add ID int identity(1,1) go--更新表A的 CODE update tA set CODE='NEWCODE'+cast(ID as varchar) go--创建表C create table tC(A_ID int,B_CODE varchar(10)) go--为表C插入记录 declare @i int select @i=max(len(B_CODE)) from tA set rowcount @i select id=identity(int) into #t from syscolumns a,syscolumns b set rowcount 0insert tC(A_ID,B_CODE) select a.ID,B_CODE=substring(a.B_CODE,b.id,charindex(',',a.B_CODE,b.id)-b.id) from tA a,#t b where b.id<len(a.B_CODE) and substring(','+a.B_CODE,b.id,1)=',' order by a.ID,B_CODEdrop table #t go--删除表A的多余字段 B_CODE alter table tA drop column B_CODE go--显示处理结果 select * from tA select * from tC go--删除测试 drop table tA,tB,tC/*--测试结果CODE ID ---------- ----------- NEWCODE1 1 NEWCODE2 2 NEWCODE3 3(所影响的行数为 3 行)A_ID B_CODE ----------- ---------- 1 A 1 B 1 C 2 A 3 A 3 B(所影响的行数为 6 行) --*/
create table tA(B_CODE varchar(50),CODE varchar(10))
insert tA select 'A,B,C,','NEWCODE'
union all select 'A,' ,'NEWCODE2'
union all select 'A,B,' ,'NEWCODE3'create table tB(CODE varchar(10))
insert tB select 'A'
union all select 'B'
union all select 'C'
union all select 'D'
go--处理--为表A添加ID字段
alter table tA add ID int identity(1,1)
go--更新表A的 CODE
update tA set CODE='NEWCODE'+cast(ID as varchar)
go--创建表C
create table tC(A_ID int,B_CODE varchar(10))
go--为表C插入记录
declare @i int
select @i=max(len(B_CODE)) from tA
set rowcount @i
select id=identity(int) into #t from syscolumns a,syscolumns b
set rowcount 0insert tC(A_ID,B_CODE)
select a.ID,B_CODE=substring(a.B_CODE,b.id,charindex(',',a.B_CODE,b.id)-b.id)
from tA a,#t b
where b.id<len(a.B_CODE) and substring(','+a.B_CODE,b.id,1)=','
order by a.ID,B_CODEdrop table #t
go--删除表A的多余字段 B_CODE
alter table tA drop column B_CODE
go--显示处理结果
select * from tA
select * from tC
go--删除测试
drop table tA,tB,tC/*--测试结果CODE ID
---------- -----------
NEWCODE1 1
NEWCODE2 2
NEWCODE3 3(所影响的行数为 3 行)A_ID B_CODE
----------- ----------
1 A
1 B
1 C
2 A
3 A
3 B(所影响的行数为 6 行)
--*/