Manualyl update autoincemental ID field

Question

Manualyl update autoincemental ID field

pelsebubb

Right there with Babe

Points: 771
More actions
September 18, 2013 at 1:28 am

#395506

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.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

pelsebubb Right there with Babe Points: 771 More actions · Answer 2

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

set IDENTITY_Insert tablename ON on the table.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

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/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

pelsebubb Right there with Babe Points: 771 More actions · Answer 5

Got it working with Insert now, i just had to specify dbo.tablename..

Thanks alot guys, i love this site. 🙂