Hi Jessica Thanks for your attention. Regarding your questions: Yes, LocationID is the primary key of this table. Create script for source table : SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE TABLE [dbo].[Locations]( [LocationID] [hierarchyid] NOT NULL, [LocationHierarchyLevel] AS ([LocationID].[GetLevel]()) PERSISTED, [LocationType] [tinyint] NOT NULL, [LocationSubtype] [tinyint] NULL, [LocationDescription] [varchar](25) NOT NULL, [Classification] [int] NULL, [Active] [bit] NOT NULL, [PropagateActivationSettings] [bit] NOT NULL, [OrderBy] [smallint] NOT NULL, [ImageCollectionID] [int] NULL, [GeographicData] [geography] NULL, [ConcurrencyCheck] [timestamp] NOT NULL, CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED ( [LocationID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_Locations_Type] UNIQUE NONCLUSTERED ( [LocationID] ASC, [LocationType] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
Create script for target table : SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Locations]( [LocationID] [hierarchyid] NOT NULL, [LocationHierarchyLevel] AS ([LocationID].[GetLevel]()) PERSISTED, [LocationType] [tinyint] NOT NULL, [LocationSubtype] [tinyint] NULL, [LocationDescription] [varchar](25) NOT NULL, [Classification] [int] NULL, [Active] [bit] NOT NULL, [PropagateActivationSettings] [bit] NOT NULL, [OrderBy] [smallint] NOT NULL, [ImageCollectionID] [int] NULL, [GeographicData] [geography] NULL, [ConcurrencyCheck] [timestamp] NOT NULL, CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED ( [LocationID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_Locations_Type] UNIQUE NONCLUSTERED ( [LocationID] ASC, [LocationType] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO Screenshots with Value Details for records with id '/5/2/' in both tables. As you can see, SQL Data Compare is not matching these records but both have the same id:
[image] [image]
I performed a comparison using the following queries in Sql server management studio: SELECT COUNT(*) FROM caromil_v2_prod.dbo.locations [source] LEFT JOIN caromil_v2dev.dbo.Locations [target] ON [source].locationId = [target].LocationID WHERE [target].LocationID IS NULL SELECT COUNT(*) FROM caromil_v2_prod.dbo.locations [source] RIGHT JOIN caromil_v2dev.dbo.Locations [target] ON [source].locationId = [target].LocationID WHERE [source].LocationID IS NULL SELECT COUNT(*) FROM caromil_v2_prod.dbo.locations [source] INNER JOIN caromil_v2dev.dbo.Locations [target] ON [source].locationId = [target].LocationID WHERE [source].Classification <> [target].Classification SELECT COUNT(*) FROM caromil_v2_prod.dbo.locations [source] INNER JOIN caromil_v2dev.dbo.Locations [target] ON [source].locationId = [target].LocationID WHERE [source].Classification = [target].Classification Obtaining the following results: Only in source (query 1):172 Only in target (query 2):1 In both but different:2 Identical:418 Running the comparison with SQL Data Compare, the result is the following:
Only in source (query 1):920
Only in target (query 2):749
In both but different:3
Identical:440
I will really appreciate your help.
/ comments
- Community
- SQL Data Compare
- Data comparison fails when table primary key is oh hierarchyId type
Hi JessicaThanks for your attention. Regarding your questions:Yes, LocationID is the primary key of this table.Create script for source table :SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO
CREATE T...
0 votes
