?
Solved

Insert SQL statement help needed.

Posted on 2015-02-17
6
Medium Priority
?
96 Views
Last Modified: 2015-02-17
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
Comment
Question by:bfuchs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 40615821
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40615828
"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
 
LVL 4

Author Comment

by:bfuchs
ID: 40615836
@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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 12

Expert Comment

by:Nathan Riley
ID: 40615838
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
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 40615843
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40615847
" 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question