March 15, 2017 at 3:09 am
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).
March 15, 2017 at 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.
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
March 15, 2017 at 3:42 am
GilaMonster - Wednesday, March 15, 2017 3:23 AMI'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
March 15, 2017 at 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?
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
March 15, 2017 at 3:53 am
GilaMonster - Wednesday, March 15, 2017 3:49 AMWhat, 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
March 15, 2017 at 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.
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
March 15, 2017 at 4:15 am
GilaMonster - Wednesday, March 15, 2017 4:04 AMThe 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 employeeOr, 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
March 15, 2017 at 5:23 am
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
March 15, 2017 at 5:26 am
GilaMonster - Wednesday, March 15, 2017 4:04 AMThe 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 employeeOr, 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.
March 15, 2017 at 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.
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
March 15, 2017 at 5:37 am
GilaMonster - Wednesday, March 15, 2017 5:28 AMThat'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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy