IDENTITY_INSERT is set to OFF error

gogetsome
gogetsome used Ask the Experts™
on
Hello, I'm trying to update a table in one database from the same table in another database with the statement below. I get the following error:

Msg 544, Level 16, State 1, Line 3
Cannot insert explicit value for identity column in table 'Accounts' when IDENTITY_INSERT is set to OFF.

How can I get past this error and insert the data?




insert into zoomdev..accounts
(partnerid,
accountNumber,
accounttype,
companyname,
companytype,
firstname,
lastname,
ubi,
address1,
address2,
city,
state,
zip,
countrycode,
phone,
fax,
url,
email,
username,
password,
rowcreated
)
select 
partnerid,
accountNumber,
accounttype,
companyname,
companytype,
firstname,
lastname,
ubi,
address1,
address2,
city,
state,
zip,
countrycode,
phone,
fax,
url,
email,
username,
password,
rowcreated
from zoomdata..accounts
where accountid not in (select accountid from  zoomdev..accounts)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
Awarded 2008
Awarded 2008
Commented:
run this first:

set identity_insert tablename on

then after the insert statement

set IDENTITY_INSERT tablename off
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
note: the reference is actually incomplete: they forgot to put the OFF:
SET IDENTITY_INSERT products OFF

after the insert.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Commented:
Before your TSql script add this line,
SET IDENTITY_INSERT zoomdev..accounts ON
Awarded 2008
Awarded 2008

Commented:
Im confused to how I wouldn't have gotten any points on this post...

Author

Commented:
Why? the first solution was correct...
Awarded 2008
Awarded 2008

Commented:
right, did you look at the time of the posts?  mine even showed you how to do it w/o having to look up a link.  you can split points.

Author

Commented:
No, sorry I did not look at the time. If I gave you the points then angelIII would be upset. It's a no win for me.
Awarded 2008
Awarded 2008

Commented:
angel wouldn't be upset....trust me.  :)
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
sorry that I did not join the "discussion" yet, but I got 2 emails from each of your comments, and I had to post that to EE office people/bugs section.

gogetsome,
 I am a bit surprised, as you are not a new member of EE, you should know the "how and when" to split points?
 
 so far, I count 2 votes to reopen the question and split the points.
 if you agree, I can activation my harry potter powers and reopen the questions

chapmandew,
  as MVP (which I think to remember you are), can you check the link,
  I posted some community content. do you see that?

a3

Author

Commented:
Yes AngelIII please wave your wond and split the points. Sorry guys.
Awarded 2008
Awarded 2008

Commented:
I's:  Yes, I caught that...very good catch.  Have you started looking at the private MVP forums?  If you join in, you can post those types of catches and they're happy to make the changes.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial