CODEWITHSUNDEEP
sqlsql-serverdatetime
Red Devil
Red Devil

Reputation: 2393

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

Upvotes: 0

Views: 85

Answers (2)

Anton
Anton

Reputation: 2882

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'

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

The typical way of fixing this problem is to declare the primary key column as an identity column and to let the database provide the value. It then would not be included in the update.

Does that solution work for you?

Upvotes: 0

Related Questions