| Author |
Topic |
|
iamlerxt
Starting Member
11 Posts |
 Posted - 2006-06-05 : 23:44:22
|
| I wrote a script to create a couple of new tables, but I forgot to include IDENTITY (1,1) in the script. Tables have been distributed to customers already. Is there a way to add turn identity on and add the identity seed and increment to an existing column in an existing table without having to recreate the table??Thanks. |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-06-06 : 01:51:30
|
| get the maximum value for that field, use that as the new seed so you won't get any conflicts with your existing valuesyou can go to EM, open the table in design mode, then select the field and select Yes on the identity propertyHTH--------------------keeping it simple... |
 |
|
|
cmdr_skywalker
Posting Yak Master
159 Posts |
Posted - 2006-06-06 : 02:11:16
|
| You can use DBCC CHECKIDENT or use ALTER TABLE. I recommend DBCC CHECKIDENT.May the Almighty God bless us all! |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-06-06 : 02:34:20
|
dbcc checkident only works for existing identity column  --------------------keeping it simple... |
|
|
|
CSK
Constraint Violating Yak Guru
489 Posts |
Posted - 2006-06-06 : 02:49:21
|
| I prefer alter table tbl1 add slno int identity(1,1)Because it Will Set the no upto last record while alter table.What gurus am i rightThanksKK |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-06-06 : 03:08:13
|
| i'm not a guru, but that will add a new column with identity property on--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-06 : 04:21:45
|
"not a guru my foot"Do you fancy that as a custom title Jen?  |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-06-06 : 04:29:02
|
| Do this in EM as Jen saidMadhivananFailing to plan is Planning to fail |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-06-06 : 04:29:20
|
such a funny yak  --------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-06 : 04:31:12
|
| Yeah, that would be a good title too! |
|
|
|
iamlerxt
Starting Member
11 Posts |
Posted - 2006-06-06 : 10:32:15
|
| Thanks for all the responses, but I really need to do this with a query, not in EM. I need to be able to just send a script or add a script to my installs so that my customers don't have to do anything. My software would run the script automatically. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-06-06 : 10:49:34
|
| go to EM, change the column to identity but don't save the change. Instead "save change script". Do that for all the tables you need to deploy. That is probably the easiest way to get it done. It does involve recreating the tables but you have no choice. Alter table can't add identity property to an existing column.Be One with the OptimizerTG |
|
|
|
|
Identity seed