I am trying to recover a record accidentally deleted in a table, from a backup table (with the same structure).
when trying to execute the below I am getting the following error.
set IDENTITY_INSERT patients on
insert into HomeCare.dbo.Patients
select * from backup_homecare.dbo.Patients
where ID not in
(select id from HomeCare.dbo.Patients)
set IDENTITY_INSERT patients off
Msg 8101, Level 16, State 1, Line 5
An explicit value for the identity column in table 'HomeCare.dbo.Patients' can only be specified when a column list is used and IDENTITY_INSERT is ON.
What is the correct SQL or what am I missing here?