Impossible to Insert or Update with identity in table
-
I am building a code to insert value in tables from the same tables (in another database) using bcp and bulk insert.
Doing so, some tables with identity cannot update or inserted, and query analyzer show the follwing error for every table with identity:
Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'flield_identity'.
INSERT INTO .......
Server: Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'tablename' can only be specified when a column list is used and IDENTITY_INSERT is ON.
My question is: How can I Update or Insert a table with identity. (I'm using a temp table to make transfer)
Thank
-
I would suggest reading BOL for IDENTITY properties. You can use this:
SET IDENTITY_INSERT tablename OFF
but if you duplicate a value, you will have serious problems when you set it back ON.
I wasn't born stupid - I had to study.
-
Your going to have issues with this unless you want to keep the identity values consistent across both datases.
If you want to keep them consistent, then just set
Identity_Insert mytable On
Insert Rows
Identity_Insert mytable Off
On your updates you need to remove the Field_Identity field from your set portion of your update clause.
-
Exactly correct. You will want to use Flow Control to accomplish this:
IF EXIST( SELECT TOP 1 IdentityField FROM Table WHERE @IdentityField = Identity)
BEGIN
UPDATE
END
ELSE
BEGIN
SET IDENTITY_INSERT Table ON
INSERT
SET IDENTITY_INSERT Table OFF
END
I wasn't born stupid - I had to study.
-
I have just tried set: SET IDENTITY_INSERT Table ON/have to delete OFF, but it doesn't work anyway.
I have found out that in INSERT and UPDATE statement I have to delete code referring to identity column and everything work.
any other suggestion to help me.....
thank
-
Let us stop guessing, post the actual code and some sample data so that we can see what's really going on.
-
As expressed above you need to clarify what are you doing with CODE and DATA samples. It is very difficult to help you if you don't cooperate
* Noel -
This seems to happen more often to usual today... Wonder what's happening in the world to cause that
. -
War of the Worlds just opened.., I would guess that is the cause. The aliens from Scientology (sp) are coming back....
I wasn't born stupid - I had to study.
-
Gonna have to go see that film soon... Seems to be worth it
.Thanx for the info on the sudden bizzare lack of information on the questions.
-
Good to know
* Noel -
Dont't worry be happy....
I'll make an example.....but sometimes I am not in front of PC......to cooperate....
Cooperation is the best way to solve problems.....
-
Yup, see you tomorrow.
-
I am taking off too
* Noel
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply