Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kirknew2SQL
Posting Yak Master
194 Posts |
 Posted - 2008-02-14 : 16:48:45
|
| I have a table MyTable that has a column named MyTableID. This table was imported from a spread sheet. MyTableID is a data type int. This is the primary key and these values must be kept. The MyTableID values are not contiguous.I have appended also appended rows to MyTable that now need to have a PK assigned. I want to make MyTableID an identity column:• But sense identity is a computed column I can't modify the existing column to a computed column.• I can’t create a new identity column and delete the old one because the original ID values must be retained.How can I create an identity column that retains the existing values and auto populates the rows that don’t have a value? |
|
|
jdaman
Constraint Violating Yak Guru
354 Posts |
Posted - 2008-02-14 : 17:17:53
|
| You could create a new table with MyTableId as an identity column, set identity insert on, insert your existing MyTableIds (and other data), set identity insert off, and then insert your new rows without MyTableIds into the table and it will auto assign the ids. |
 |
|
|
kirknew2SQL
Posting Yak Master
194 Posts |
Posted - 2008-02-14 : 17:26:34
|
| I like your solution. Though I was hoping there was some SQL command or SP to do this for me. LOL THanks :) |
|
|
|
talleyrand
Starting Member
35 Posts |
Posted - 2008-02-14 : 21:46:56
|
| I haven't gotten my virtual up yet for home so this is just a stab in the dark, but would something like this work? Syntax might be a touch wonky.ALTER TABLE MyTableALTER COLUMN MyTableID int identity(50000, 1) NOT NULL PRIMARY KEYThis of course assumes the MyTableID has no null values and 50000 is larger than your current key. Although it might figure that out automagically, I really don't know. |
|
|
|
kirknew2SQL
Posting Yak Master
194 Posts |
Posted - 2008-02-15 : 10:57:56
|
| I'll give that a try. thanks |
|
|
|
talleyrand
Starting Member
35 Posts |
Posted - 2008-02-15 : 13:28:35
|
| Now that I'm in front of SS, I was apparently talking out of my posterior. Some changes are allowed in place for columns but adding the identity property isn't one of them so run with jdaman's suggestion. |
|
|
|
kirknew2SQL
Posting Yak Master
194 Posts |
Posted - 2008-02-15 : 13:57:12
|
| Will do. Thanks for the update. |
|
|
|
loderunner
Starting Member
1 Post |
Posted - 2009-06-24 : 13:52:23
|
| In SQL 2005 it is easy! I had the same problem and had to maintain the existing values in an identity column. After inserting the data into the non-identity column, I just used the SSMS GUI to open the table in Design view and set the identity property to True on the column. It worked and even started incrementing at the right place.Who knows if there is script to do this? |
|
|
|
|
|
|
How to change an int column to identity column?