update b
set serviceno = a.serviceno,
idno = a.idno
from Fleet_StagingLive.dbo.stg_Employee a
inner join Fleet_DataMartLive.dbo.Dim_Employee b
on a.nodeid = b.nodeid
where b.nodeid <> -1
and b.nodeid <> -2
and a.IDNo = '580829881659'
The table structure
CREATE TABLE [dbo].[Dim_Employee](
[Key_Employee] [int] IDENTITY(1,1) NOT NULL,
[Key_Department] [int] NOT NULL,
[ServiceNo] [varchar](50) NOT NULL,
[IDNo] [varchar](50) NOT NULL,
[First_Name] [varchar](50) NOT NULL,
[Last_Name] [varchar](50) NOT NULL,
[Email] [varchar](100) NOT NULL,
[Telephone] [varchar](50) NULL,
[Mobile] [varchar](50) NULL,
[PhysicalAddress] [varchar](500) NULL,
[PostalAddress] [varchar](500) NULL,
[HasLoco] [int] NOT NULL,
[IsDriver] [int] NOT NULL,
[Key_BusinessUnit_WorkCentre] [int] NULL,
[Job_Code] [varchar](50) NULL,
[AllLoco] [int] NULL,
[NodeId] [int] NULL,
[AdhocLocoCode] [varchar](10) NULL,
CONSTRAINT [PK_Dim_Employee] PRIMARY KEY CLUSTERED
(
[Key_Employee] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_Dim_Employee] UNIQUE NONCLUSTERED
(
[ServiceNo] ASC,
[IDNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
What I have tried:
I only excluded the duplicates to allow the job or sql server agent to run and complete ...
Below is the error I get :
Violation of UNIQUE KEY constraint 'IX_Dim_Employee'. Cannot insert duplicate key in object 'dbo.Dim_Employee'.The duplicate key value is (400613-CDMG0176, 580829881659)