Red Devil Red Devil - 3 months ago 8
SQL Question

Giving 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 it is giving me 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 wont 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'