violation of primary key constraint in insert query not touching the PK column

Question

I have a query that inserts record in a table. the primary key column of that table is an Identity field that auto-increments. the select part of the query will have duplicates, but I have an an unique constraint with ignore_dup_key=on on fields (city_nm, prov_en_nm) that should skip them on insert. this used to work fine, but for some reason now it gives me this message. this is the first time I try it since the database was moved from a 2012 sql server to a 2014 if that can have an impact

Violation of PRIMARY KEY constraint 'Dim_city_province_country_pk'. Cannot insert duplicate key in object 'HD_DtlClm.dim_city_province_country_t'. The duplicate key value is (###). (where ### is an ID, a different one every time I run it)

Here is the query.

INSERT INTO HD_DtlClm.[dim_city_province_country_t] (
    city_nm, prov_en_nm, prov_fr_nm, contry_fr_nm, contry_en_nm
    )
SELECT  gr_mbr_city_nm, PROV_ENG_NM, PROV_FR_NM, CONTRY_ENG_NM, CONTRY_FR_NM
FROM    isu.gr_dentl_clm_v 
    LEFT JOIN HD_DtlClm.province_information_t 
    ON gr_dentl_clm_v.gr_mbr_prov_cd = HD_DtlClm.province_information_t.PROV_CLM_CD
UNION
SELECT  gr_prvdr_city_nm, PROV_ENG_NM, PROV_FR_NM, CONTRY_ENG_NM, CONTRY_FR_NM
FROM    isu.gr_dentl_clm_v 
    LEFT JOIN HD_DtlClm.province_information_t 
    ON gr_dentl_clm_v.gr_prvdr_prov_cd IN (HD_DtlClm.province_information_t.PROV_ENG_CD, HD_DtlClm.province_information_t.PROV_CLM_CD)

Any idea why I get this error that I didn't get in the past?

EDIT to add primary key creation script:

ALTER TABLE [HD_DtlClm].[dim_city_province_country_t] ADD  CONSTRAINT [Dim_city_province_country_pk] PRIMARY KEY CLUSTERED 
(   [cpc_key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,     IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

EDIT2 to add table creation script

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [HD_DtlClm].[dim_city_province_country_t](
    [cpc_key] [int] IDENTITY(1,1) NOT NULL,
    [city_nm] [char](50) NOT NULL,
    [prov_en_nm] [char](50) NULL,
    [prov_fr_nm] [char](50) NULL,
    [contry_en_nm] [char](75) NULL,
    [contry_fr_nm] [char](75) NULL,
    [create_ts] [datetime] NOT NULL,
    [update_ts] [datetime] NOT NULL,
 CONSTRAINT [Dim_city_province_country_pk] PRIMARY KEY CLUSTERED 
(
    [cpc_key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [dim_city_province_country_ak1] UNIQUE NONCLUSTERED 
(
    [city_nm] ASC,
    [prov_en_nm] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [HD_DtlClm].[dim_city_province_country_t] ADD  DEFAULT (getdate()) FOR [create_ts]
GO

ALTER TABLE [HD_DtlClm].[dim_city_province_country_t] ADD  DEFAULT (getdate()) FOR [update_ts]
GO

Answer 1

Try running: DBCC CHECKIDENT ('HD_DtlClm.[dim_city_province_country_t]'); look at the results returned in the messages tab & make sure the current identity value is equal to or higher than the current column value. NB running this may even fix the problem itself.

To expand: looks like something had reseeded your identity column, so the insert was causing duplicates to be picked up. Don't think there's any way to check historically what changed it; the most likely candidates are the DBCC CHECKIDENT command with RESEED option, or a TRUNCATE operation (will reseed to the original value).