请教一个SQL语句

[wangzi761129]

有A、B两表，比较两表中在ID相同时，其他字段的内容是否一致。如果不一致，将不一致的内容，生成一个新表。

[lemonchen]

用存储过程吧

[pengdali]

select * into 新表 from a where not exists (select 1 from b where id=a.id and (列1<>a.列1 or 列2<>a.列2 or 列3<>a.列3...))

insert 新表 select * from b where not exists (select 1 from a where id=b.id and (列1<>b.列1 or 列2<>b.列2 or 列3<>b.列3...))


select * from 新表

[txlicenhe]

最笨的办法：
insert into 新表
select * from A,B where a.id = b.id
and (a.f1 <> a.f1 or a.f2 <> b.f2 ...)

[CrazyFor]

select * into 新表 from a inner join b on a.id=b.id
where a.col1<>b.col2 and a.col2<>b.col2 ....

[gmlxf]

select * into newtable
from A.* where
not exists(select 1 from A,B where A.col1=B.col1 and ... and A.ID=B.ID)

[gmlxf]

select * into newtable
from A where
not exists(select 1 from A,B where A.col1=B.col1 and ... and A.ID=B.ID)

[realgz]

同意楼上的说法
declare @exec varchar(8000)
set @exec =''
select @exec=@exec+' a.'+name+' =b.'+name+' and' from syscolumns where id=object_id('[your table]') and name<>'id'
set @exec=left(@exec,len(@exec)-3)
set @EXEC='select * into [new table] from [your table] a where
(select count(*) from c where c.id=a.id)>1 and
not exists ( select 1 from [your table] b where '+@exec+' and b.id=a.id) '
exec(@exec)
select * from [new table]

[realgz]

看错了 还以为是同一张表:)，原来是两张表。。。。。。郁闷，不写了。。。。。。

[killxtt]

大力那个写法是不是应该把or全都换成and阿？不然逻辑好像不对吧

[friendliu]

写一个存储过程。。比较相同的ID的其它字段是否相同。。
create proc test
as
declare @id int
begin
declare cursor_id cursor for
select id from a
open cursor_id
fetch next from cursor_id into @id
while @fetch_status=0
begin
if not exists(select 1 from a,b where a.id=b.id and a.col1=b.col1 and a.col2.b.col2 and.........)
begin
insert into C select * from a where id=@id
insert into C select * from b where id=@id
end
end

CLOSE cursor_test
DEALLOCATE cursor_test


end