写出sql语句

[prettyBoy2002]

两个表A和B结构完全一样,现在知道:(1)表名(2)表A和表B的主键都为myid.
不知道:两表的其余字段名.
问题:写出如A表某记录的myid与B表的myid相同, 则用B表的记录去更新A表的相应记录.

[sdhdy]

select 表A.myid into #temp from 表B where 表A.myid=表B.myid
delete 表A from 表B where 表A.myid=表B.myid
insert 表A select * from 表B where 表B.myid in (select myid from #temp)
drop table #temp

[sdhdy]

--写错了
select 表A.myid into #temp from 表A where 表A.myid=表B.myid
delete 表A from 表B where 表A.myid=表B.myid
insert 表A select * from 表B where 表B.myid in (select myid from #temp)
drop table #temp

[CrazyFor]

select 表A.myid into #temp from 表B where 表A.myid=表B.myid
delete 表A from 表B where 表A.myid=表B.myid
set identity_insert 表A on
insert 表A select * from 表B where 表B.myid in (select myid from #temp)
set identity_insert 表A off

[sdhdy]

--先把两表相同的myid保存到临时表中
select 表A.myid into #temp from 表A,表B where 表A.myid=表B.myid
--删除 表A中和表B有相同myid的记录
delete 表A from 表B where 表A.myid=表B.myid
--插入表A中，表B和表A中有相同myid的记录
insert 表A select * from 表B where 表B.myid in (select myid from #temp)
--去掉临时表
drop table #temp

[CrazyFor]

更正:
select 表A.myid into #temp from 表A,表B where 表A.myid=表B.myid
delete 表A from 表B where 表A.myid=表B.myid
set identity_insert 表A on
insert 表A select * from 表B where 表B.myid in (select myid from #temp)
set identity_insert 表A off

[amtyuranus]

用一存储过程实现

找出所有 a.myid=b.myid的记录，然后记下a.myid，
然后删除掉这些记录，然后在插入就可以了

[amtyuranus]

或者几个存储过程实现

select 表A.myid into #temp from 表A,表B where 表A.myid=表B.myid
delete 表A from 表B where 表A.myid=表B.myid
insert into 表A select * from 表B where 表B.myid in (select myid from #temp)

[zjcxc]

用生成SQL语句的方法就行啦:

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',a.['+name+']=b.['+name+']'
from syscolumns
where object_id('表A')=id and name<>'myid' and status<>0x80
set @sql='update 表A set '+substring(@sql,2,8000)
+' from 表A a inner join 表B b on a.myid=b.myid'
exec(@sql)

[zjcxc]

如果你的两个表字段对应是指位置对应,而不是名称都相同的话,就用:
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',a.['+a.name+']=b.['+b.name+']'
from (
select name,colid syscolumns
where object_id('表A')=id and name<>'myid' and status<>0x80
) a inner join (
select name,colid syscolumns
where object_id('表B')=id and name<>'myid' and status<>0x80
) b on a.colid=b.colid
set @sql='update 表A set '+substring(@sql,2,8000)
+' from 表A a inner join 表B b on a.myid=b.myid'
exec(@sql)

[zjcxc]

如果你的两个表字段对应是指位置对应,而不是名称都相同的话,就用:
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',a.['+a.name+']=b.['+b.name+']'
from (
select name,colid syscolumns
where object_id('表A')=id and name<>'myid' and status<>0x80
) a inner join (
select name,colid syscolumns
where object_id('表B')=id and name<>'myid' and status<>0x80
) b on a.colid=b.colid
set @sql='update 表A set '+substring(@sql,2,8000)
+' from 表A a inner join 表B b on a.myid=b.myid'
exec(@sql)