Click here to Skip to main content
15,887,135 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SQL
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 --fms
and b.nodeid <> -2  --ctrack
and a.IDNo = '580829881659'
The table structure
SQL
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)
Posted
Updated 11-Oct-18 21:01pm
v3
Comments
CHill60 11-Oct-18 10:58am    
The error message is fairly clear ... you are trying to update the table by setting serviceno = 400613-CDMG0176 and idno = 5806195814589 and that combination of values is already on the table Dim_Employee.
Member 13495182 12-Oct-18 3:46am    
Hi CHill60, I know what you saying since this is an update if the record already exist it suppose to return 0 rows affected ... the error has something to do with the Indexes so I'm not sure what to fix here
ZurdoDev 12-Oct-18 8:12am    
You are creating a duplicate record. You need to fix that.
Bryian Tan 12-Oct-18 9:01am    
did you check if (400613-CDMG0176, 580829881659) exist in the table?
CHill60 2-Nov-18 12:31pm    
"since this is an update if the record already exist it suppose to return 0 rows affected" ... no. An update can only work if a record already exists. If the record does not exist it will return 0 rows affected. It doesn't matter if you are changing an existing record or inserting a new one - you already have a record where serviceno = 400613-CDMG0176 and idno = 5806195814589 and you can't have another one.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900