Manualyl update autoincemental ID field

  • Hi.

    I have a table where the UserID is set to primary key and autoincrement, i need to import data to that table, and i need the same UserID fields from my old DB.

    The problem is that i can't write to that field since first it is the primary key, and second it seeams to be read only and it autoincrements.

    I have removed the Primary key, but it's still read only. I plan to set the column back to it's original state as long as i can import some data one time to it.

    I have checked that the table has no dependencies, i only need the userID to be writable in the table.

  • You can explicitly insert into that field if you turn IDENTITY_INSERT on. There's no way to update an existing value in a column that's marked as identity.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • i still get that the column is read only after execution of :

    set IDENTITY_Insert tablename ON on the table.

  • pelsebubb (9/18/2013)


    i still get that the column is read only after execution of :

    set IDENTITY_Insert tablename ON on the table.

    what error you are getting?

    Please post the code that you are trying

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • pelsebubb (9/18/2013)


    i still get that the column is read only after execution of :

    set IDENTITY_Insert tablename ON on the table.

    Are you trying to insert or update? If insert, then you can use Identity_Insert and it will allow you to specify the values for that column. If you're trying to update, there's no way to make a column with the identity property updatable.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Got it working with Insert now, i just had to specify dbo.tablename..

    Thanks alot guys, i love this site. 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply