Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Data Compare - Some tables missing

When doing a Data Compare using SQL Server Data Tools through VS 2013 pro, I have a scenario where some tables appear to be getting missed out.

What I mean by that is there is data in TableA on the source server but no data in the equivalent table on the destination server.

However the results window doesn't even display a row for TableA.

Also if I try to filter the results in the next step prior to pressing "Finish", TableA doesn't appear as an option to filter by. It's almost as if the table doesn't exist on the destination server. I've verified that is does by:

  • Connecting through SSMS and viewing the list of tables
  • Running SELECT * FROM information_schema.tables

In both cases I can see the table is listed.

Has anyone else seen this? What am I missing?

like image 698
lee_mcmullen Avatar asked Jun 29 '15 12:06

lee_mcmullen


People also ask

Can we compare two tables in SQL?

Comparing the Results of the Two Queries Let us suppose, we have two tables: table1 and table2. Here, we will use UNION ALL to combine the records based on columns that need to compare. If the values in the columns that need to compare are the same, the COUNT(*) returns 2, otherwise the COUNT(*) returns 1.

How can I compare two tables in different columns in SQL?

We can use union to compare the columns once we can have the union of both the tables. It can quickly check what are the data missing or changed in either table. It is capable of handling null values which cannot be handled by where clause. This is only used when we have the same type of tables.


1 Answers

Data can be compared only if you know what records from tables must be compared. Comparer uses PK to know what records to compare. If your table doesn't have a PK (or at least a unique index) it ill be missing from the tables list.

You can solve it by creating a PK yourself (just for comparison)

EDIT

  1. A coworker got a hard time recently due to someone explicit excluding some tables from the comparison project and committing it to git. So check it if it's not a new project.

  2. I recently was tasked to compare tables without PK and found HASHBYTES to be a new friend, also there are not unique rows in the tables and to solve it I used ROW_NUMBER with PARTITION, see below snipet.

    SELECT Row_number() 
             OVER ( 
               partition BY [hashid] 
               ORDER BY [hashid]) AS RowNumber, 
           * 
    INTO   [dbo].[mytable_temp] 
    FROM   (SELECT Hashbytes('SHA2_512', (SELECT x.* 
                                          FOR xml raw)) AS [HASHID], 
                   * 
            FROM   [dbo].[mytable] AS x) AS y 

    go 

    ALTER TABLE [dbo].[mytable_temp] 
      ALTER COLUMN [hashid] VARBINARY(900) NOT NULL 

    ALTER TABLE [dbo].[mytable_temp] 
      ALTER COLUMN [rownumber] BIGINT NOT NULL 

    go 

    ALTER TABLE [dbo].[mytable_temp] 
      ADD CONSTRAINT pk_id PRIMARY KEY ([hashid], [rownumber]) 

    go 

That way I can create PK based on a hash calculated with the entire row content.

Obs:. Note I'm comparing MyTable_TEMPand not MyTable. that way I can leave it untounched.

like image 140
jean Avatar answered Oct 07 '22 17:10

jean