Post reply

Impossible to Insert or Update with identity in table

  • June 28, 2005 at 10:03 am

    #164141

    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

  • June 28, 2005 at 10:20 am

    #569034

    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.

  • June 28, 2005 at 10:28 am

    #569040

    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.

     

  • June 28, 2005 at 10:37 am

    #569049

    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.

  • June 29, 2005 at 1:44 am

    #569222

    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

     

  • June 29, 2005 at 8:18 am

    #569341

    Let us stop guessing, post the actual code and some sample data so that we can see what's really going on.

  • June 29, 2005 at 10:24 am

    #569410

    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

  • June 29, 2005 at 10:28 am

    #569414

    This seems to happen more often to usual today... Wonder what's happening in the world to cause that .

  • June 29, 2005 at 10:41 am

    #569433

    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.

  • June 29, 2005 at 10:46 am

    #569439

    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.

  • June 29, 2005 at 10:47 am

    #569441

    Good to know

     


    * Noel

  • June 29, 2005 at 3:45 pm

    #569626

    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.....

  • June 29, 2005 at 3:48 pm

    #569631

    Yup, see you tomorrow.

  • June 29, 2005 at 3:55 pm

    #569635

    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