--tablediffdrop table ta drop table tbcreate table ta(id int primary key,name varchar(50)) insert into ta select 1,'a' insert into ta select 2,'b' insert into ta select 3,'c'create table tb(id int primary key,name varchar(50))insert into tb select 2,'b' insert into tb select 3,'c' insert into tb select 4,'d' insert into tb select 5,'e'select * into tb from ta where 1=2 select * from tbcmd tablediff路径 cd c:\program files\microsoft sql server\90\com tablediff -sourceserver "." -sourcedatabase "my2" -sourceschema "dbo" -sourcetable "ta" -sourceuser "sa" -sourcepassword "aa" -destinationserver "." -destinationdatabase "my2" -destinationschema "dbo" -destinationtable "tb" -destinationuser "sa" -destinationpassword "aa" -f "c:\test.sql" -o "c:\test2.txt"
补充一句,我用的是sql server2005
看看这样可以不--如果查询结果为空说明完全一致 --如果不为空,查询出的数据就是不一致的数据 select * from a where a+b+c not in (select a+b+c from 查询结果)
--> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([a] int,[b] int,[c] int,[d] int) insert [tb] select 1,2,3,4 union all select 2,1,3,3 union all select 3,2,2,5 --> 测试数据:[A] if object_id('[A]') is not null drop table [A] go create table [A]([a] int,[b] int,[c] int) insert [A] select 1,2,3 union all select 2,1,3 union all select 3,2,2
--select a,b,c from tb这个假设为你要查询的 if (select count(*) from (select a,b,c from tb) m)= (select count(*) from (select a,b,c from tb) m join A on m.a=A.a and m.b=A.b and m.c=A.c) print '数据相同' else print '数据不同'/* 数据相同 */
drop table tbcreate table ta(id int primary key,name varchar(50))
insert into ta select 1,'a'
insert into ta select 2,'b'
insert into ta select 3,'c'create table tb(id int primary key,name varchar(50))insert into tb select 2,'b'
insert into tb select 3,'c'
insert into tb select 4,'d'
insert into tb select 5,'e'select * into tb from ta where 1=2
select * from tbcmd
tablediff路径
cd c:\program files\microsoft sql server\90\com
tablediff -sourceserver "." -sourcedatabase "my2" -sourceschema "dbo" -sourcetable "ta" -sourceuser "sa" -sourcepassword "aa" -destinationserver "." -destinationdatabase "my2" -destinationschema "dbo" -destinationtable "tb" -destinationuser "sa" -destinationpassword "aa" -f "c:\test.sql" -o "c:\test2.txt"
--如果不为空,查询出的数据就是不一致的数据
select * from a where a+b+c not in (select a+b+c from 查询结果)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] int,[b] int,[c] int,[d] int)
insert [tb]
select 1,2,3,4 union all
select 2,1,3,3 union all
select 3,2,2,5
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([a] int,[b] int,[c] int)
insert [A]
select 1,2,3 union all
select 2,1,3 union all
select 3,2,2
--select a,b,c from tb这个假设为你要查询的
if (select count(*) from (select a,b,c from tb) m)=
(select count(*) from (select a,b,c from tb) m join A on m.a=A.a and m.b=A.b and m.c=A.c)
print '数据相同'
else
print '数据不同'/*
数据相同
*/