function - Violation of Primary Key Constraint - SQL Server

Question

In my SQL Server, I've created a database and I am trying to export the values from the 2 tables listed below into a new table that I created DM_Location within the database.

But I keep getting this Error

Violation of PRIMARY KEY constraint 'PK__DM_Locat__BA8F50140E575579'. Cannot insert duplicate key in object 'dbo.DM_Location'. The duplicate key value is (1).

My code:

INSERT INTO DM_Location (Authority_Id, FacilityAddress, FacilityPostcode)
    SELECT AuthorityId, FacilityAddress, FacilityPostcode
    FROM dbo.2015_2016 
    UNION 
    SELECT DISTINCT AuthorityId, FacilityAddress, FacilityPostcode 
    FROM dbo.2016_2017

Any help would be appreciated! Thanks

Answer 1

The problem is that you have two tables with Authority_Id value 1, but they have diferenct values in FacilityAddress or FacilityPostcode. Since you have a primary key constraint in table DM_Location this produces a Primary Key violation error.

To fix the problem you can drop the primary key constraint:

ALTER TABLE DM_Location
DROP CONSTRAINT PK__DM_Locat__BA8F50140E575579;

Of course if you want to keep a primary key on the table you will have to handle the error produced but that really depends on why you would want it.