How to compare data between two table in different databases using Sql Server 2008

Question

I have two database s  named DB1 and DB2 in Sql server 2008   These two database s have the same tables and same table data also   However  I want to check if there are any differences between the data in these tables    Could anyone help me with a script for this

User · Answer

select   from DB1 dbo Table a inner join DB2 dbo Table b on b PrimKey   a PrimKey  where a FirstColumn  lt  gt  b FirstColumn       Checksum that Matt recommended is probably a better approach to compare columns rather than comparing each column

User · Answer

If the database are in the same server use  DatabaseName   Owner   TableName  format when accessing a table that resides in a different database   Eg   DB1   dbo   TableName   If databases in different server look at on Creating Linked Servers  SQL Server Database Engine

User · Answer

In order to compare two databases  I ve written the procedures bellow  If you want to compare two tables you can use procedure  CompareTables   Example    EXEC master dbo CompareTables  DB1    dbo    table1    DB2    dbo    table2    If you want to compare two databases  use the procedure  CompareDatabases   Example    EXEC master dbo CompareDatabases  DB1    DB2    Note   - I tried to make the procedures secure  but anyway  those procedures are only for testing and debugging  - If you want a complete solution for comparison use third party like  Visual Studio        USE  master  GO  create proc  dbo   CompareDatabases       FirstDatabaseName nvarchar 50        SecondDatabaseName nvarchar 50      as begin     -- Check that databases exist     if not exists SELECT name FROM sys databases WHERE name  FirstDatabaseName          return 0     if not exists SELECT name FROM sys databases WHERE name  SecondDatabaseName          return 0      declare  result table  TABLE NAME nvarchar 256       SET NOCOUNT ON     insert into  result EXEC   Select distinct TABLE NAME from      FirstDatabaseName      INFORMATION SCHEMA COLUMNS                                         Where TABLE SCHEMA   dbo                                    intersect                                  Select distinct TABLE NAME from      SecondDatabaseName      INFORMATION SCHEMA COLUMNS                                         Where TABLE SCHEMA   dbo           DECLARE  TABLE NAME nvarchar 256      DECLARE curseur CURSOR FOR         SELECT TABLE NAME FROM  result     OPEN curseur     FETCH curseur INTO  TABLE NAME         WHILE   FETCH STATUS   0             BEGIN                 print  TABLE        TABLE NAME                 EXEC master dbo CompareTables  FirstDatabaseName   dbo    TABLE NAME   SecondDatabaseName   dbo    TABLE NAME                 FETCH curseur INTO  TABLE NAME             END         CLOSE curseur     DEALLOCATE curseur     SET NOCOUNT OFF end GO      USE  master  GO  CREATE PROC  dbo   CompareTables       FirstTABLE CATALOG nvarchar 256        FirstTABLE SCHEMA nvarchar 256        FirstTABLE NAME nvarchar 256        SecondTABLE CATALOG nvarchar 256        SecondTABLE SCHEMA nvarchar 256        SecondTABLE NAME nvarchar 256      AS BEGIN     -- Verify if first table exist     DECLARE  table1 nvarchar 256     FirstTABLE CATALOG          FirstTABLE SCHEMA          FirstTABLE NAME     DECLARE  return status int     EXEC  return status   master dbo TableExist  FirstTABLE CATALOG   FirstTABLE SCHEMA   FirstTABLE NAME     IF  return status   0         BEGIN             PRINT  table1       Table Not FOUND              RETURN 0         END        -- Verify if second table exist     DECLARE  table2 nvarchar 256     SecondTABLE CATALOG          SecondTABLE SCHEMA          SecondTABLE NAME     EXEC  return status   master dbo TableExist  SecondTABLE CATALOG   SecondTABLE SCHEMA   SecondTABLE NAME     IF  return status   0         BEGIN             PRINT  table2       Table Not FOUND              RETURN 0         END      -- Compare the two tables     DECLARE  sql AS NVARCHAR MAX      SELECT  sql                           SELECT        table1       as  Table    FROM      FirstTABLE CATALOG          FirstTABLE SCHEMA          FirstTABLE NAME                          EXCEPT                      SELECT        table1       as  Table    FROM      SecondTABLE CATALOG          SecondTABLE SCHEMA          SecondTABLE NAME                                                UNION                                            SELECT        table2       as  Table    FROM      SecondTABLE CATALOG          SecondTABLE SCHEMA          SecondTABLE NAME                          EXCEPT                      SELECT        table2       as  Table    FROM      FirstTABLE CATALOG          FirstTABLE SCHEMA          FirstTABLE NAME                                 DECLARE  wrapper AS NVARCHAR MAX     if exists       sql         char 10               sql     ORDER BY 2      Exec  wrapper  END GO      USE  master  GO  CREATE PROC  dbo   TableExist       TABLE CATALOG nvarchar 256        TABLE SCHEMA nvarchar 256        TABLE NAME nvarchar 256      AS BEGIN     IF NOT EXISTS SELECT name FROM sys databases WHERE name  TABLE CATALOG          RETURN 0      declare  result table  TABLE SCHEMA nvarchar 256   TABLE NAME nvarchar 256       SET NOCOUNT ON     insert into  result EXEC  Select TABLE SCHEMA  TABLE NAME from      TABLE CATALOG      INFORMATION SCHEMA COLUMNS       SET NOCOUNT OFF      IF EXISTS SELECT TABLE SCHEMA  TABLE NAME FROM  result                 WHERE TABLE SCHEMA  TABLE SCHEMA AND TABLE NAME  TABLE NAME          RETURN 1      RETURN 0 END  GO

User · Answer

If both database on same server  You can check similar tables by using following query    select        fdb name  sdb name  from        FIRSTDBNAME sys tables fdb        join SECONDDBNAME sys tables sdb       on fdb name   sdb name -- compare same name tables order by        1        By listing out similar table you can compare columns schema using sys columns view   Hope this helps you

User · Answer

select    from         select  T1  T          from DB1 dbo Table       except       select  T2  T          from DB2 dbo Table        as T union all select    from         select  T2  T          from DB2 dbo Table       except       select  T1  T          from DB1 dbo Table        as T ORDER BY 2 3 4       1  -- make T1 and T2 to be close in output 2 3 4 are UNIQUE KEY SEGMENTS  Test code  declare  T1 table  ID int  declare  T2 table  ID int   insert into  T1 values 1   2  insert into  T2 values 2   3   select    from         select         from  T1       except       select         from  T2        as T union all select    from         select         from  T2       except       select         from  T1        as T  Result  ID ----------- 1 3  Note  It can take long time to compare big table  when developing  quot tuned quot  solution or refactorig  which will give same result as REFERERCE - it may be wise to chekc simple parameters first  like select count t    from      select count    c0  SUM BINARY CHECKSUM    1000000  c1 FROM T REF TABLE     -- select 12345 c0  -214365454 c1 -- constant values FROM T REF TABLE     except     select count      SUM BINARY CHECKSUM    1000000  FROM T WORK COPY    t  When this is empty  you have probably things under controll  and may be you can modify when you fail you will see  quot constant values FROM T REF quot  to isert to save even more time for next check

User · Answer

Comparing the two Databases in SQL Database  Try this Query it may help   SELECT T  name  AS  table name   AC  name  AS  column name    TY  name  AS     system data type FROM        Database Name 1     sys  tables  AS T      INNER JOIN     Database Name 1     sys  all columns  AC ON T  object id    AC  object id           INNER JOIN     Database Name 1     sys  types  TY ON AC  system type id    TY  system type id      EXCEPT SELECT T  name  AS  table name   AC  name  AS  column name   TY  name  AS system data type FROM       Database Name 2    sys  tables  AS T      INNER JOIN    Database Name 2    sys  all columns  AC ON T  object id    AC  object id       INNER JOIN    Database Name 2    sys  types  TY ON AC  system type id    TY  system type id

User · Answer

I   d really suggest that people who encounter this problem go and find a third party database comparison tool    Reason     these tools save a lot of time and make the process less error prone    I   ve used comparison tools from ApexSQL   Diff and Data Diff  but you can   t go wrong with other tools marc s and Marina Nastenko already pointed out   If you   re absolutely sure that you are only going to compare tables once then SQL is fine but if you   re going to need this from time to time you   ll be better off with some 3rd party tool   If you don   t have budget to buy it then just use it in trial mode to get the job done   I hope new readers will find this useful even though it   s a late answer

User · Answer

I v done things like this using the Checksum    function  In essance it creates a row level checksum on all the columns data  you could then compare the checksum of each row for each table to each other  use a left join  to find rows that are different   Hope that made sense     Better with an example      select   from    select checksum    as chk  userid as k from UserAccounts  as t1 left join    select checksum    as chk  userid as k from UserAccounts  as t2 on t1 k   t2 k where t1 chk  lt  gt  t2 chk

User · Answer

Another solution  non T-SQL   you can use tablediff utility   For example if you want to compare two tables  Localitate  from two different servers  ROBUH01  amp  ROBUH02  you can use this shell command   C  Program Files Microsoft SQL Server 100 COM gt tablediff -sourceserver ROBUH01 -s ourcedatabase SIM01 -sourceschema dbo -sourcetable Localitate -destinationserver  ROBUH02 -destinationschema dbo -destinationdatabase SIM02 -destinationtable Lo calitate   Results   Microsoft  R  SQL Server Replication Diff Tool Copyright  c  2008 Microsoft Corporation User-specified agent parameter values   -sourceserver ROBUH01  -sourcedatabase SIM01  -sourceschema dbo  -sourcetable Localitate  -destinationserver ROBUH02  -destinationschema dbo  -destinationdatabase SIM02  -destinationtable Localitate   Table  SIM01   dbo   Localitate  on ROBUH01 and Table  SIM02   dbo   Localitate   on ROBUH02 have 10 differences    Err Id Dest   Only 21433 Dest   Only 21434 Dest   Only 21435 Dest   Only 21436 Dest   Only 21437 Dest   Only 21438 Dest   Only 21439 Dest   Only 21441 Dest   Only 21442 Dest   Only 21443  The requested operation took 9 9472657 seconds  ------------------------------------------------------------------------

[sql] How to compare data between two table in different databases using Sql Server 2008?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008