3

Possible Duplicate:
set identity on the column

I have a table let's say Messages with a column name Serno. Serno keeps the serial number for each message. However system is very old and creates this serial number using max(serno) command. Now, I am trying to change this column to an identity but I don't want to loose the current serial numbers for each message.

Is there a solution for this.

I already try to creatae a new identity column and copy the values from serno there but it doesn't work. Neither creating a new table work as database will give new serial numbers for each message and I don't want that.

Any ideas are Welcome.

Thanks in advance

M.A

Community
  • 1
  • 1
M.A
  • 273
  • 2
  • 8
  • Please check this article. I think it will help you solve your issue. [SQL SERVER – Add or Remove Identity Property on Column](http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/) – manish chavda Sep 10 '12 at 12:11

2 Answers2

1

have you tried opening the table in design view, selecting your column, and setting the IdentitySpecification to Yes (this may take a while if you have many rows, as it internally generates a complete copy of the table)

paul
  • 21,653
  • 1
  • 53
  • 54
  • Yes I have tried this. It gives me an error that I have to re-create the table.Thanks. – M.A Sep 10 '12 at 11:38
  • 1
    In SSMS studio, select `Tools -> Options -> Designers`, then uncheck the box that is labelled `Prevent saving changes that require table re-creation` – paul Sep 10 '12 at 12:25
  • I was afraid that doing so, I would loose any constraints and indexes. However I test it and works like a charm! Everything in place! Thanks so much for your help!! – M.A Sep 11 '12 at 05:18
1

You cannot alter the existing column and make it as an identity column.

You need to create a temporary table and insert the values to that. Finally you have to drop the actual table and rename the temp table to actual table

CREATE TABLE dbo.NewTable(ID int IDENTITY(1, 1),<other columns>)

SET IDENTITY_INSERT dbo.NewTable ON

INSERT  INTO dbo.NewTable ( Id, <other columns>)
SELECT  Id,  <other columns> FROM    <actual_table>
go

SET IDENTITY_INSERT dbo.NewTable OFF
go

DROP TABLE your_table
go

Exec sp_rename 'NewTable', 'actual_table'
Joe G Joseph
  • 23,518
  • 5
  • 56
  • 58
  • Since the ID is an identity it won't let me to update this field! – M.A Sep 10 '12 at 11:50
  • @M.A: once you do SET IDENTITY_INSERT dbo.NewTable ON , then it will work – Joe G Joseph Sep 10 '12 at 11:51
  • Since this is an insert it might work... I will try this and let u know the results. Thanks for your help!! – M.A Sep 10 '12 at 11:56
  • You **can** alter the existing column and make it as an identity column. See my answer in the possible duplicate question. – Martin Smith Sep 10 '12 at 12:15
  • I am using this : SET IDENTITY_INSERT dbo.MessagesOLD ON INSERT INTO MessagesOLD select * from Messages And I get the error: Msg 8101, Level 16, State 1, Line 1 An explicit value for the identity column in table 'MessagesOLD' can only be specified when a column list is used and IDENTITY_INSERT is ON. – M.A Sep 11 '12 at 04:55
  • Martin, Can you point me to the question as I can't find it? Thanks – M.A Sep 11 '12 at 04:59
  • @M.A. [My answer here](http://stackoverflow.com/a/6094907/73226). BTW use the `@` symbol when replying to people otherwise they do not get notified. – Martin Smith Sep 12 '12 at 07:46