Red Devil Red Devil - 3 months ago 12
SQL Question

Getting an error because of primary key

Test

--------------------------------------------------------------------------------
Workershiftid ShiftID Startdate EntityId EndDate
--------------------------------------------------------------------------------
149 1 2016-08-01 00:00:00 1 2016-08-31 00:00:00
150 2 2016-08-01 00:00:00 4 2016-08-31 00:00:00
151 3 2016-08-01 00:00:00 5 2016-08-31 00:00:00
152 4 2016-08-01 00:00:00 7 2016-08-31 00:00:00
--------------------------------------------------------------------------------


I am expecting the output like

--------------------------------------------------------------------------------
Workershiftid ShiftID Startdate EntityId EndDate
--------------------------------------------------------------------------------
153 1 2016-09-01 00:00:00 1 2016-09-31 00:00:00
154 2 2016-09-01 00:00:00 4 2016-09-31 00:00:00
155 3 2016-09-01 00:00:00 5 2016-09-31 00:00:00
156 4 2016-09-01 00:00:00 7 2016-09-31 00:00:00
--------------------------------------------------------------------------------


I am running this query:

declare @start datetime
declare @end datetime

SELECT @start = DATEADD(month, DATEDIFF(month, 0, getdate()), 0)
SELECT @end = DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))

insert into test(workershiftid, shiftid, startdate, entityid, enddate)
select
workershiftid, shiftid, @start, entityid, @end
from
test
where
startdate = '2016-08-01'


But I get an error:


Violation of PRIMARY KEY constraint 'PK_M_AttendanceWorkerShift'. Cannot insert duplicate key in object 'dbo.M_AttendanceWorkerShift'.


I understand that workershiftid is primary key and it won't allow duplicates. I want to know how to fix this problem

Answer

Assuming you have IDENTITY on PK (workershiftid)...

You need to remove workershiftid from the insert script, as sql server will automatically generate a new workershiftid for all the new rows

Final script:

insert into [dbo].[test](EntityID, startdate,shiftid, IsGroup, EndDate) 
 select EntityID,@start, shiftid, IsGroup, @end 
 from [dbo].[test] 
 where startdate='2016-08-01'
Comments