SQL data comparison - some tables are missing

Question

When you are comparing data using SQL Server Data Tools through VS 2013 pro, I have a scenario where some tables seem to be skipped.

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

However, the row for TableA not displayed in the result window.

Also, if I try to filter the results in the next step before clicking Finish, TableA will not appear as an option for filtering. It is almost as if the table did not exist on the target server. I checked that there are:

• Connect via SSMS and view a list of tables
• Running SELECT * FROM information_schema.tables

In both cases, I see the table is indicated.

Has anyone else seen this? What am I missing?

Answer 1

Data can be compared only if you know which records from the tables you want to compare. Comparer uses PK to know which entries to compare. If your table does not have PK (or at least a unique index), it may not be in the list of tables.

You can solve this problem by creating a PC yourself (for comparison only)

EDIT

1. The employee recently had a hard time because someone explicitly excluded some tables from the comparison project and sent it to git. So check if this is not a new project.

2. Recently, I was commissioned to compare tables without PK, and I found that HASHBYTES is a new friend. In addition, there are no unique rows in the tables, and to solve them I used ROW_NUMBER with PARTITION, see the fragment below.

  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 for the entire contents of the string.

Obs:. Notice I'm comparing MyTable_TEMP , not MyTable . so I can leave this unattended.