你的位置:首页 > 数据库

[数据库]sql server:compare data from two tables


--Comparing data between two tables in SQL Server--Create two Tables--CREATE TABLE TableA(ID Int, ProuctName Varchar(256))GOCREATE TABLE TableB(ID Int, ProuctName Varchar(256))GOINSERT INTO TableA VALUES(1,'A')INSERT INTO TableA VALUES(2,'B')INSERT INTO TableA VALUES(3,'D')INSERT INTO TableA VALUES(4,'E')INSERT INTO TableA VALUES(5,'F')INSERT INTO TableA VALUES(6,'塗聚文')INSERT INTO TableB VALUES(1,'A')INSERT INTO TableB VALUES(2,NULL)INSERT INTO TableB VALUES(3,'C')INSERT INTO TableB VALUES(4,'F')INSERT INTO TableB VALUES(5,'D')INSERT INTO TableB VALUES(6,'塗聚文')--Get rows from TableA that are not found in TableB--SELECT ProuctName FROM TableAEXCEPTSELECT ProuctName FROM TableB--Get rows from TableB that are not found in TableA--SELECT ProuctName FROM TableBEXCEPTSELECT ProuctName FROM TableA--两个表共同不同的记录SELECT ProuctNameFROM(SELECT * FROM TableAUNION ALL select * from TableB) dataGROUP BY ProuctNameHAVING count(*)!=2---http://blogs.msdn.com/b/ramoji/archive/2010/07/01/how-to-compare-data-between-two-tables-in-sql-server.aspxCREATE PROCEDURE CompareTableData(@SourceDB sysname,@SourceSchema sysname,@SourceTable sysname,@TargetDB sysname,@TargetSchema sysname,@TargetTable sysname)ASBEGINSET NOCOUNT ONDECLARE @SQL NVarchar(Max)DECLARE @ColList Varchar(Max)--Concatenate the column list by excluding the data types that can't be used in comparision--SET @SQL = 'SELECT @ColList = CASE WHEN @ColList IS NULL THEN '''' ELSE @ColList + '','' END + SC.Name FROM ' + @SourceDB + '.sys.columns SC 'SET @SQL = @SQL + ' INNER JOIN ' + @SourceDB + '.sys.Types ST ON SC.system_type_id = ST.system_type_id WHERE object_id = object_id(@SourceTable) 'SET @SQL = @SQL + ' AND ST.Name NOT IN (''