| Author |
Topic |
|
michaelxvo
Starting Member
47 Posts |
 Posted - 2006-04-26 : 11:48:44
|
| I have a table with a column: column1 int primary key. I would like to alter this column to be identity. how do I write in T-SQL: alter table test alter column1 (I get stuck here)thank you |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-26 : 13:37:05
|
| You can't use ALTER TABLE to modify a column to be identity. Perform the change in Enterprise Manager but prior to saving it, click the save change script button. Check out the code. That's the same code that would be required if you were to write the code by yourself in Query Analyzer. So what you have to do is:1. create a new table with the desired layout2. move your data into this new table3. drop your old table4. rename the new table to the old table5. add back any constraints and/or indexesTara Kizeraka tduggan |
 |
|
|
michaelxvo
Starting Member
47 Posts |
Posted - 2006-04-26 : 13:54:53
|
| I can do the change to be identity with EM. The problem is this:The old table has column1 identity value.I migrate to the new table AND I WANT THE NEW TABLE to KEEP the old identity values and CONTINUE the IDENTITY VALUES FROM the old table. In order for me to do that, I create the new table without identity and migrate data to the new table.after migrating the data, now I want to activate the that old identity on column1 |
|
|
|
michaelxvo
Starting Member
47 Posts |
Posted - 2006-04-26 : 13:55:18
|
| I can do the change to be identity with EM. The problem is this:The old table has column1 identity value.I migrate to the new table AND I WANT THE NEW TABLE to KEEP the old identity values and CONTINUE the IDENTITY VALUES FROM the old table. In order for me to do that, I create the new table without identity and migrate data to the new table.after migrating the data, now I want to activate the that old identity on column1 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-26 : 14:00:50
|
| Please see my steps on how to do this in Query Analyzer. You just need to translate the steps into code. Enterprise Manager can generate this code for you as mentioned in my first post.Tara Kizeraka tduggan |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-26 : 14:39:21
|
| Forgot one thing. When you copy your data from the old table into the new table, you'll need to use SET IDENTITY_INSERT ON.SET IDENTITY_INSERT NewTable ONINSERT INTO NewTable (Column1, ...)SELECT Column1, ...FROM OldTableSET IDENTITY_INSERT NewTable OFFTara Kizeraka tduggan |
|
|
|
michaelxvo
Starting Member
47 Posts |
Posted - 2006-04-26 : 16:00:01
|
| thank you very much for your code. |
|
|
|
cjohnson6
Starting Member
1 Post |
Posted - 2006-05-04 : 15:23:03
|
| Can this same procedure be done with SQL Server 2005? I can't seem to recreate this as SQL 2005 now uses that "SQL Server Mgmt Studio" utility. Basically the same concept as Enterprise Mgr but not quite. Any thoughts? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-04 : 15:28:22
|
| cjohnson6,Please post what code you are running in Management Studio.Tara Kizeraka tduggan |
|
|
|
sampro
Starting Member
4 Posts |
Posted - 2008-01-22 : 10:36:31
|
| i m using sqlserver 2005i have a table that has the primary key set as identity i want to make that off insert one row and then again make that on and i want to do that through sql script.please can anyone specify it i need that urgently |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
how to alter column to be identity