代码之家 › 专栏 › 技术社区 › MicMit

需要用于SQL Server的比较表实用程序

diff comparison sql-server-2005 sql-server

MicMit · 技术社区 · 14 年前

用于SQL server的命令行或库“比较表”实用程序,具有到.Net中文件的全面差异输出

我找不到那样的东西。商业或免费(XSQL Lite适合我的情况)工具在网格中显示差异,并有可能导出到CSV。同时,它们在从命令行运行时生成同步SQL脚本。我需要的是一个适合解析的综合报表(XML、HTML)输出,以便能够在应用程序中显示类似的差异网格(更新每列的旧值/新值、添加行的所有值、删除行的所有值等).

3 回复 | 直到 8 年前

KM. 14 年前

因为听起来你只想在应用程序中显示差异,所以只需编写自己的查询,这并不难,下面是一个示例:

DECLARE @TableA table (RowID int, Col1 int, Col2 varchar(5), Col3 datetime)
DECLARE @TableB table (RowID int, Col1 int, Col2 varchar(5), Col3 datetime)
set nocount on
INSERT @TableA VALUES( 1,111,'AAA','1/1/2010')
INSERT @TableA VALUES( 2,222,'BBB','1/1/2010')
INSERT @TableA VALUES( 3,333,'CCC','1/1/2010')
INSERT @TableA VALUES( 4,444,'DDD','1/1/2010')
INSERT @TableA VALUES( 5,555,'EEE','1/1/2010')
INSERT @TableA VALUES( 6,666,'FFF','1/1/2010')
INSERT @TableA VALUES( 7,777,'GGG','1/1/2010')
INSERT @TableA VALUES( 9,888,'HHH','1/1/2010')
INSERT @TableA VALUES(10,111,'III','1/1/2010')

INSERT @TableB VALUES( 1,111,'AAA','1/1/2010')
INSERT @TableB VALUES( 3,333,'CCC','1/1/2010')
INSERT @TableB VALUES( 4,444,'DD' ,'1/1/2010')
INSERT @TableB VALUES( 5,555,'EEE','2/2/2010')
INSERT @TableB VALUES( 6,666,'FFF','1/1/2010')
INSERT @TableB VALUES( 7,777,'GGG','1/1/2010')
INSERT @TableB VALUES( 8,888,'ZZZ','1/1/2010')
INSERT @TableB VALUES( 9,888,'HHH','1/1/2010')
INSERT @TableB VALUES(10,111,'III','1/1/2010')
set nocount off

SELECT
    a.RowID, CASE WHEN b.RowID IS NULL THEN 'A' ELSE '' END AS RowsOnlyExistsIn
        ,a.Col1,b.Col1, CASE WHEN a.Col1=b.Col1 OR (COALESCE(a.Col1,b.Col1) IS NULL) THEN 'N' ELSE 'Y' END AS Col1Diff
        ,a.Col2,b.Col2, CASE WHEN a.Col2=b.Col2 OR (COALESCE(a.Col2,b.Col2) IS NULL) THEN 'N' ELSE 'Y' END AS Col2Diff
        ,a.Col3,b.Col3, CASE WHEN a.Col3=b.Col3 OR (COALESCE(a.Col3,b.Col3) IS NULL) THEN 'N' ELSE 'Y' END AS Col3Diff
    FROM @TableA                 a
        LEFT OUTER JOIN @TableB  b On a.RowID=b.RowID
UNION ALL
SELECT
    b.RowID, 'B' AS RowsOnlyExistsIn
        ,null,b.Col1, 'Y' AS Col1Diff
        ,null,b.Col2, 'Y' AS Col2Diff
        ,null,b.Col3, 'Y' AS Col3Diff
    FROM @TableB                 b
    WHERE b.RowID NOT IN (SELECT RowID FROM @TableA)
ORDER BY 1

RowID       RowsOnlyExistsIn Col1        Col1        Col1Diff Col2  Col2  Col2Diff Col3                    Col3                    Col3Diff
----------- ---------------- ----------- ----------- -------- ----- ----- -------- ----------------------- ----------------------- --------
1                            111         111         N        AAA   AAA   N        2010-01-01 00:00:00.000 2010-01-01 00:00:00.000 N
2           A                222         NULL        Y        BBB   NULL  Y        2010-01-01 00:00:00.000 NULL                    Y
3                            333         333         N        CCC   CCC   N        2010-01-01 00:00:00.000 2010-01-01 00:00:00.000 N
4                            444         444         N        DDD   DD    Y        2010-01-01 00:00:00.000 2010-01-01 00:00:00.000 N
5                            555         555         N        EEE   EEE   N        2010-01-01 00:00:00.000 2010-02-02 00:00:00.000 Y
6                            666         666         N        FFF   FFF   N        2010-01-01 00:00:00.000 2010-01-01 00:00:00.000 N
7                            777         777         N        GGG   GGG   N        2010-01-01 00:00:00.000 2010-01-01 00:00:00.000 N
8           B                NULL        888         Y        NULL  ZZZ   Y        NULL                    2010-01-01 00:00:00.000 Y
9                            888         888         N        HHH   HHH   N        2010-01-01 00:00:00.000 2010-01-01 00:00:00.000 N
10                           111         111         N        III   III   N        2010-01-01 00:00:00.000 2010-01-01 00:00:00.000 N

(10 row(s) affected)

当然,您需要动态地生成它,以便可以比较任意两个表。此查询将获取任何表的列:

SELECT
    *
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE 
        TABLE_CATALOG   ='database'
        AND TABLE_SCHEMA='dbo'
        AND TABLE_NAME  ='yourtable'
    ORDER BY ORDINAL_POSITION

下面是一个快速的动态sql版本(我试图拉入所有的PK列并使用它们来动态连接表,但我只在具有1pk列的表上进行了测试):

CREATE TABLE TableA (RowID int primary key, Col1 int, Col2 varchar(5), Col3 datetime)
CREATE TABLE TableB (RowID int primary key, Col1 int, Col2 varchar(5), Col3 datetime)
set nocount on
INSERT TableA VALUES( 1,111,'AAA','1/1/2010')
INSERT TableA VALUES( 2,222,'BBB','1/1/2010')
INSERT TableA VALUES( 3,333,'CCC','1/1/2010')
INSERT TableA VALUES( 4,444,'DDD','1/1/2010')
INSERT TableA VALUES( 5,555,'EEE','1/1/2010')
INSERT TableA VALUES( 6,666,'FFF','1/1/2010')
INSERT TableA VALUES( 7,777,'GGG','1/1/2010')
INSERT TableA VALUES( 9,888,'HHH','1/1/2010')
INSERT TableA VALUES(10,111,'III','1/1/2010')

INSERT TableB VALUES( 1,111,'AAA','1/1/2010')
INSERT TableB VALUES( 3,333,'CCC','1/1/2010')
INSERT TableB VALUES( 4,444,'DD' ,'1/1/2010')
INSERT TableB VALUES( 5,555,'EEE','2/2/2010')
INSERT TableB VALUES( 6,666,'FFF','1/1/2010')
INSERT TableB VALUES( 7,777,'GGG','1/1/2010')
INSERT TableB VALUES( 8,888,'ZZZ','1/1/2010')
INSERT TableB VALUES( 9,888,'HHH','1/1/2010')
INSERT TableB VALUES(10,111,'III','1/1/2010')
set nocount off

动态SQL语句

DECLARE @TableA   sysname
       ,@TableB   sysname
       ,@SQLa     varchar(max)
       ,@SQLb     varchar(max)
       ,@SQL      varchar(max)
SELECT @TableA='TableA'
      ,@TableB='TableB'
      ,@SQLa=NULL
      ,@SQLb=NULL

DECLARE @PKs      table (RowID int identity(1,1) primary key, PkColumn sysname)
DECLARE @index_id int
       ,@PK       sysname
       ,@i        int
SELECT @index_id=index_id from sys.indexes where object_id=OBJECT_ID(@TableA) AND is_primary_key=1

SELECT @PK=''
      ,@i=0
while (@PK is not null)
BEGIN
    SET @i=@i+1
    SELECT @PK = index_col(@TableA, @index_id, @i)
    IF @PK IS NULL BREAK
    INSERT INTO @PKs (PkColumn) VALUES (@PK)
END

SELECT @SQLa=''
      ,@SQLb=''''+@TableB+''' '
SELECT
    @SQLa=@SQLa+' ,a.'+COLUMN_NAME+',b.'+COLUMN_NAME+', CASE WHEN a.'+COLUMN_NAME+'=b.'+COLUMN_NAME+' OR (COALESCE(a.'+COLUMN_NAME+',b.'+COLUMN_NAME+') IS NULL) THEN ''N'' ELSE ''Y'' END AS '+COLUMN_NAME+'Diff '
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME  =@TableA
    ORDER BY ORDINAL_POSITION

SELECT
    @SQLb=@SQLb+' ,null,b.'+COLUMN_NAME+', ''Y'' AS '+COLUMN_NAME+'Diff'
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME  =@TableA
    ORDER BY ORDINAL_POSITION

SET @SQL='SELECT CASE WHEN b.'+(SELECT PkColumn FROM @PKs WHERE RowID=1)+' IS NULL THEN '''+@TableA+''' ELSE '''' END AS RowsOnlyExistsIn '
        +@SQLa
        +'FROM '+@TableA+' a LEFT OUTER JOIN '+@TableB+' b ON '

SELECT
    @SQL=@SQL+ CASE WHEN RowID!=1 THEN 'AND ' ELSE '' END +'a.'+PkColumn+'=b.'+PkColumn+' '
    FROM @PKs

SET @SQL=@SQL+' UNION ALL SELECT '
        +@SQLb
        +' FROM '+@TableB+' b LEFT OUTER JOIN '+@TableA+' A ON '

SELECT
    @SQL=@SQL+ CASE WHEN RowID!=1 THEN 'AND ' ELSE '' END +'b.'+PkColumn+'=a.'+PkColumn+' '
    FROM @PKs

SET @SQL=@SQL+'WHERE a.'+(SELECT PkColumn FROM @PKs WHERE RowID=1)+' IS NULL ORDER BY 2,3'

EXEC(@SQL)

RowsOnlyExistsIn RowID       RowID       RowIDDiff Col1        Col1        Col1Diff Col2  Col2  Col2Diff Col3                    Col3                    Col3Diff
---------------- ----------- ----------- --------- ----------- ----------- -------- ----- ----- -------- ----------------------- ----------------------- --------
TableB           NULL        8           Y         NULL        888         Y        NULL  ZZZ   Y        NULL                    2010-01-01 00:00:00.000 Y
                 1           1           N         111         111         N        AAA   AAA   N        2010-01-01 00:00:00.000 2010-01-01 00:00:00.000 N
TableA           2           NULL        Y         222         NULL        Y        BBB   NULL  Y        2010-01-01 00:00:00.000 NULL                    Y
                 3           3           N         333         333         N        CCC   CCC   N        2010-01-01 00:00:00.000 2010-01-01 00:00:00.000 N
                 4           4           N         444         444         N        DDD   DD    Y        2010-01-01 00:00:00.000 2010-01-01 00:00:00.000 N
                 5           5           N         555         555         N        EEE   EEE   N        2010-01-01 00:00:00.000 2010-02-02 00:00:00.000 Y
                 6           6           N         666         666         N        FFF   FFF   N        2010-01-01 00:00:00.000 2010-01-01 00:00:00.000 N
                 7           7           N         777         777         N        GGG   GGG   N        2010-01-01 00:00:00.000 2010-01-01 00:00:00.000 N
                 9           9           N         888         888         N        HHH   HHH   N        2010-01-01 00:00:00.000 2010-01-01 00:00:00.000 N
                 10          10          N         111         111         N        III   III   N        2010-01-01 00:00:00.000 2010-01-01 00:00:00.000 N

(10 row(s) affected)

IMHO 14 年前

一、假设要比较数据而不是schema,我假设表在diff数据库中。我不想在SQL中这样做,而是在通用的.Net数据集中加载数据,并遍历表对象、列和行。这样,一段代码就可以用于任何数据库表,不需要笨拙的动态SQL,但是缺点可能是perf-hit,因为您需要将数据加载到数据集中—在这种情况下,只需在SQL server本身上运行代码并将结果文件转储到文件共享。

user122991 user122991 14 年前

开源DiffKit将完成所有这些,除了.NET部分。

www.diffkit.org网站