update t2 set t2.d = t1.a + '|' + t1.b + '|' + t1.c from B表 t2 Join A表 t1 on t2.id = t1.id
--这样? create table tb(id int,a varchar(10),b varchar(10),c varchar(10)) insert into tb select 1,'xx','yy','zz' union all select 2,'aa','bb','cc' union all select 3,'dd','ee','ff' gocreate function dbo.fc_str(@id varchar(10)) returns varchar(100) as begin declare @sql varchar(1000) set @sql='' select @sql=@sql+cast(a as varchar(100))+'|'+cast(b as varchar(100))+'|'+cast(c as varchar(100)) from tb where id=@id return @sql end goselect ID,dbo.fc_str(ID) as [str] from tb group by iddrop table tbdrop function dbo.fc_str
哦。是更改update b set d=a.a+'|'+a.b+'|'+a.c where a.id=b.id
B表是本来存在的。。其实是把A表的a b c 字段的数据用“|”分开 然后存到B表的D字段,条件是ID号相同的记录
那就 select id,a + '|' + b + '|' + c as d into B表 from A表
???小苦,能写全吗?我也用了select into 可是都没有成功
照你的要求,这样就已经写全了。select id,a + '|' + b + '|' + c as d into B表 from A表
insert into b select select id,a + '|' + b + '|' + c as d from a
那就再加一点代码: /* 如果B表已存在就先删除 */ if exists(select 1 from sysobjects where name = 'B表') begin drop table B表 endselect id,a + '|' + b + '|' + c as d into B表 from A表
还是不行。麻烦大家测试下可以吗你们机器里面有SQL SERVER2000 吗?
B表不能删除,因为B表中还有其他字段,,A表中和B表中均20几万记录,
create table b(id int,d varchar(10)) insert into b(id) select 1 insert into b(id) select 2 insert into b(id) select 3create table a(id int,a varchar(10),b varchar(10),c varchar(10)) insert into a select 1,'xx','yy','zz' union all select 2,'aa','bb','cc' union all select 3,'dd','ee','ff'select * from b update b set d=a.a+'|'+a.b+'|'+a.c from a,b where a.id=b.id select * from b
declare @t table(id int , a varchar(2) , b varchar(2) ,c varchar(2) ) declare @t1 table(id int ,d varchar(20))insert into @t select 1, 'xx', 'yy', 'zz' union select 2, 'aa', 'bb', 'cc' union select 3, 'dd', 'ee', 'ff' insert into @t1 select 1 , '' union select 2 , '' union select 3 , ''update @t1 set d = '|' + b.a + '|' + b.b + '|' + b.c from @t1 a , @t b where a.id = b.id select * from @t1
set t2.d = t1.a + '|' + t1.b + '|' + t1.c
from B表 t2
Join A表 t1 on t2.id = t1.id
create table tb(id int,a varchar(10),b varchar(10),c varchar(10))
insert into tb
select 1,'xx','yy','zz'
union all select 2,'aa','bb','cc'
union all select 3,'dd','ee','ff'
gocreate function dbo.fc_str(@id varchar(10))
returns varchar(100)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+cast(a as varchar(100))+'|'+cast(b as varchar(100))+'|'+cast(c as varchar(100)) from tb where id=@id
return @sql
end
goselect ID,dbo.fc_str(ID) as [str] from tb group by iddrop table tbdrop function dbo.fc_str
select id,a + '|' + b + '|' + c as d
into B表
from A表
into B表
from A表
/* 如果B表已存在就先删除 */
if exists(select 1 from sysobjects where name = 'B表')
begin
drop table B表
endselect id,a + '|' + b + '|' + c as d
into B表
from A表
insert into b(id) select 1
insert into b(id) select 2
insert into b(id) select 3create table a(id int,a varchar(10),b varchar(10),c varchar(10))
insert into a select 1,'xx','yy','zz'
union all select 2,'aa','bb','cc'
union all select 3,'dd','ee','ff'select * from b
update b set d=a.a+'|'+a.b+'|'+a.c from a,b where a.id=b.id
select * from b
declare @t1 table(id int ,d varchar(20))insert into @t
select 1, 'xx', 'yy', 'zz' union
select 2, 'aa', 'bb', 'cc' union
select 3, 'dd', 'ee', 'ff' insert into @t1
select 1 , '' union
select 2 , '' union
select 3 , ''update @t1
set d = '|' + b.a + '|' + b.b + '|' + b.c
from @t1 a , @t b
where a.id = b.id
select * from @t1