Violation of primary key constraint error when updating sql table ?

  • Hello I am using below query to update my table from another table
    ...................................
    SET IDENTITY_INSERT [dbo].[Employees] on
    INSERT INTO [dbo].[Employees]
    (EmployeeID,LastName,FirstName,Title,TitleOfCourtesy, BirthDate,HireDate,Address,City,Region,PostalCode)
    SELECT EmployeeID,LastName,FirstName,Title,TitleOfCourtesy, BirthDate,HireDate,Address,City,Region,PostalCode
    FROM backupdatabase.dbo.Employees as d
    SET IDENTITY_INSERT dbo.Employees OFF

    ..............................

    but it returns the below error

    Msg 2627, Level 14, State 1, Line 5
    Violation of PRIMARY KEY constraint 'PK_Employees'. Cannot insert duplicate key in object 'dbodbo.Employees'. The duplicate key value is (1).

  • I'm confused.
    You say you want to update this table from another DB, but the query you're running is an insert, not an update.

    The reason you're getting the  PK violation is that there's already data in the employee table with an EmployeeID of 1, and you're trying to put another record in with the same ID, hence duplicate key error.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster - Wednesday, March 15, 2017 3:23 AM

    I'm confused.
    You say you want to update this table from another DB, but the query you're running is an insert, not an update.

    The reason you're getting the  PK violation is that there's already data in the employee table with an EmployeeID of 1, and you're trying to put another record in with the same ID, hence duplicate key error.

    i want to update the table  employee from another table called 'backup.employee ' table

    how to update from one table to another in this case. 

    source table has  18 records and the destination has 21 records. but still i want to accomplish this scenario

  • What, exactly do you want to do with the rows that are:

    1) In employee, not in backup.employee?
    2) Not in employee, in backup.employee?
    3) In both?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster - Wednesday, March 15, 2017 3:49 AM

    What, exactly do you want to do with the rows that are:

    1) In employee, not in backup.employee?
    2) Not in employee, in backup.employee?
    3) In both?

    1) discard or delete from employee
    2) update or make same like backup.employee
    3) in nutshell, i want all my records in employee as same like backup.employee table

  • The answer for 2) makes no sense, you can't update when rows don't exist in one table. I'll assume you meant 'insert them' for (2) and 'update to make the same' for (3)

    You have two options.
    You could delete from Employee to remove all the rows, and then use your original query to insert the rows from backup into employee

    Or, you could run a delete to remove the rows that are in Employee but not in backup_employees, then an update to set the column values the same for the rows in both tables, and finally an insert to put the rows in employee_backup but not in employee into the employee table.
    In all three of those, you will have to join the tables or use the EXISTS/NOT EXISTS or IN/NOT IN predicates to check where the rows are.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster - Wednesday, March 15, 2017 4:04 AM

    The answer for 2) makes no sense, you can't update when rows don't exist in one table. I'll assume you meant 'insert them' for (2) and 'update to make the same' for (3)

    You have two options.
    You could delete from Employee to remove all the rows, and then use your original query to insert the rows from backup into employee

    Or, you could run a delete to remove the rows that are in Employee but not in backup_employees, then an update to set the column values the same for the rows in both tables, and finally an insert to put the rows in employee_backup but not in employee into the employee table.
    In all three of those, you will have to join the tables or use the EXISTS/NOT EXISTS or IN/NOT IN predicates to check where the rows are.

    I cannot delete as they are conflicting with  REFERENCE constraint

    delete from dbodbo.Employees
    WHERE EmployeeID between 1 and 10

  • Then you'll need to use option 2.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster - Wednesday, March 15, 2017 4:04 AM

    The answer for 2) makes no sense, you can't update when rows don't exist in one table. I'll assume you meant 'insert them' for (2) and 'update to make the same' for (3)

    You have two options.
    You could delete from Employee to remove all the rows, and then use your original query to insert the rows from backup into employee

    Or, you could run a delete to remove the rows that are in Employee but not in backup_employees, then an update to set the column values the same for the rows in both tables, and finally an insert to put the rows in employee_backup but not in employee into the employee table.
    In all three of those, you will have to join the tables or use the EXISTS/NOT EXISTS or IN/NOT IN predicates to check where the rows are.

    SET IDENTITY_INSERT [dbo].[Employees] off
    IF not EXISTS (SELECT * FROM backupdatabase.dbo.Employees
         WHERE EmployeeID between 1 and 2000)
    INSERT [dbo].[Employees]
    (EmployeeID,LastName,FirstName,Title,TitleOfCourtesy, BirthDate,HireDate,Address,City,Region,PostalCode)
    SELECT EmployeeID,LastName,FirstName,Title,TitleOfCourtesy, BirthDate,HireDate,Address,City,Region,PostalCode
    FROM backupdatabase.dbo.Employees as d
    where EmployeeID=952
    SET IDENTITY_INSERT dbo.Employees OFF

    i have one more issue now . 
    I entered a new employee record called employee id 952  in backup table and with the above query tried to insert that record to the employee database. the query was successful but it did'nt  inserted that record in the database.. i couldnt figure it out.

  • That's not going to work for what you want. That query, if there are ANY rows in employee with ID between 1 and 2000, then the insert won't run.
    You need the EXISTS in the WHERE clause of the SELECT, not a separate IF.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster - Wednesday, March 15, 2017 5:28 AM

    That's not going to work for what you want. That query, if there are ANY rows in employee with ID between 1 and 2000, then the insert won't run.
    You need the EXISTS in the WHERE clause of the SELECT, not a separate IF.

    SET IDENTITY_INSERT dbo.Employees on
    insert into Northwind2.dbo.Employees
    (EmployeeID,LastName,FirstName,Title,TitleOfCourtesy, BirthDate,HireDate,Address,City,Region,PostalCode)
    select EmployeeID,LastName,FirstName,Title,TitleOfCourtesy, BirthDate,HireDate,Address,City,Region,PostalCode
    from backupdatabase.dbo.Employees as d
    where EmployeeID=952

    Yes.. The above code did it .. 

    Now let me  try option 2 and see if i can make it work

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply