[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 102
  • Last Modified:

Insert SQL statement help needed.

Hi Experts,

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

Open in new window

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?
0
bfuchs
Asked:
bfuchs
  • 2
  • 2
  • 2
1 Solution
 
Nathan RileyFounder/CTOCommented:
select * 
into HomeCare.dbo.Patients
from backup_homecare.dbo.Patients 
where ID not in
(select id from HomeCare.dbo.Patients)

Open in new window

0
 
PortletPaulCommented:
"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."

you have not included the column list in your query

e.g.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel');

and don't use select *, use explicit column names
0
 
bfuchsAuthor Commented:
@Nathan,
I got the below when executing yours..
There is already an object named 'Patients' in the database.

@PortletPaul,
I missed that, however why is it like that, if both tables have the same structure shouldn't it be straight forward?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
Nathan RileyFounder/CTOCommented:
You could drop the existing table if there is no new data and just bring in the backup with my query above.  That will depend if this is dev or live environment.
0
 
bfuchsAuthor Commented:
Thanks PortletPaul!

@Nathan,
No, I am restoring a single record after a day of work, while that would cause all my new records to be deleted..
0
 
PortletPaulCommented:
" if both tables have the same structure shouldn't it be straight forward?"
why doesn't it think like us? I don't know :)

It is always a good idea to be explicit I'm afraid - I know it will be frustrating particularly if there are lots of fields, but usually It's possible to get a list of fields pretty easy and with a bit of copy/paste etc. whip up an insert

seems that for this particular case, when overriding an auto-incrementing field, it just insists on know what you really absolutely and explicitly want.

cheers.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now