select * from t1 checksum(firstname,lastname,address) not in(select checksum(firstname,lastname,address) from t2)
先查 连得上的:inner join 。 再not in 这个子查询。
请楼主注意一下自己结贴率--table2中与table1不匹配的记录 select * from table1 a where not exists (select 1 from table2 b where a.firstname=b.firstname and a.lastname=b.lastname and a.address=b.address)--再查table1中与table2不匹配的记录 select * from table2 b where not exists (select 1 from table1 a where a.firstname=b.firstname and a.lastname=b.lastname and a.address=b.address)
建议用tablediff ,可以生成SQL数据。 http://msdn.microsoft.com/zh-cn/library/ms162843.aspxC:\Program Files\Microsoft SQL Server\100\COM>tablediff /? Microsoft (R) SQL Server Replication Diff Tool Copyright (c) 2008 Microsoft CorporationUser-specified agent parameter values: /? Replication Diff Tool Command Line Options usage: tablediff -- Source Options -- -sourceserver Source Host -sourcedatabase Source Database -sourceschema Source Schema Name -sourcetable Source Table or View -sourceuser Source Login -sourcepassword Source Password -sourcelocked Lock the source table/view durring tablediff -- Destination Options -- -destinationserver Destination Host -destinationdatabase Destination Database -destinationschema Destination Schema Name -destinationtable Destination Table or View -destinationuser Destination Login -destinationpassword Destination Password -destinationlocked Lock the destination table/view durring tablediff -- Misc Options -- -t Timeout -c Column Level Diff -f Generate Fix SQL (You may also specify a file name and path) -q Quick Row Count -et Specify a table to put the errors into -dt Drop the error table if it exists -o Output file -b Number of bytes to read for blob data types -strict Strict compare of source and destination schema -rc Number of retries -ri Retry interval
select firstname,lastname,address from table2 except ( select firstname,lastname,address from table1 )
select * from table1 a
where not exists (select 1 from table2 b where a.firstname=b.firstname
and a.lastname=b.lastname and a.address=b.address)--再查table1中与table2不匹配的记录
select * from table2 b
where not exists (select 1 from table1 a where a.firstname=b.firstname
and a.lastname=b.lastname and a.address=b.address)
http://msdn.microsoft.com/zh-cn/library/ms162843.aspxC:\Program Files\Microsoft SQL Server\100\COM>tablediff /?
Microsoft (R) SQL Server Replication Diff Tool
Copyright (c) 2008 Microsoft CorporationUser-specified agent parameter values:
/? Replication Diff Tool Command Line Options usage: tablediff -- Source Options --
-sourceserver Source Host
-sourcedatabase Source Database
-sourceschema Source Schema Name
-sourcetable Source Table or View
-sourceuser Source Login
-sourcepassword Source Password
-sourcelocked Lock the source table/view durring tablediff -- Destination Options --
-destinationserver Destination Host
-destinationdatabase Destination Database
-destinationschema Destination Schema Name
-destinationtable Destination Table or View
-destinationuser Destination Login
-destinationpassword Destination Password
-destinationlocked Lock the destination table/view durring tablediff -- Misc Options --
-t Timeout
-c Column Level Diff
-f Generate Fix SQL (You may also specify a file name and path)
-q Quick Row Count
-et Specify a table to put the errors into
-dt Drop the error table if it exists
-o Output file
-b Number of bytes to read for blob data types
-strict Strict compare of source and destination schema
-rc Number of retries
-ri Retry interval
select firstname,lastname,address from table2 except
(
select firstname,lastname,address from table1
)