| Author |
Topic |
|
dhinasql
Posting Yak Master
195 Posts |
 Posted - 2008-04-26 : 01:56:10
|
| Hi Friends,I have a existing table named as activity, and have the column like ID,Description. I want to add the Identity for the ID column using script only.. Have any ideas how to do in sql query analyser?Thanks in Advance |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-26 : 02:07:48
|
| I dont think you cant alter a column to be identity type.One method is to add a new column of type identity and migrate the existing nonid column values to it by setting identity insert on. Then drop the nonid column and rename the newly created idcolumn using sp_rename. ALso make sure you drop all indexes, foreign keys and other constraints on the column before doing this and recreate them after. |
 |
|
|
dhinasql
Posting Yak Master
195 Posts |
Posted - 2008-04-26 : 02:12:40
|
| hi Visakh,Thanks for your reply, in the existing column i have a data, i want the same data in my table, and suppose i have a lost id is 302, here after i want to add identity for the ID filed so that whenever im inserting a data, it will automatically increase from 302.. i want to achieve this.. could you please explain more...thanks |
|
|
|
dhinasql
Posting Yak Master
195 Posts |
Posted - 2008-04-26 : 02:19:14
|
| is it possible to insert value manually for the identity column? |
|
|
|
soorajtnpki
Posting Yak Master
231 Posts |
Posted - 2008-04-26 : 02:19:43
|
| hi alter table Table1 alter column Column1 int identity(1,1)is not accepted by query analyser because it does not recognize the "identity" word... alter table Table1 add Column2 int identity(1,1) will add a new column with identity.but you want an existing column to be identity set up,in that caseyou could create another table with the esired structure, move the data with identity insert on on the new table and then rename the new table and the old tables to what you want.ok,thanks. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-04-26 : 02:21:34
|
you could just create a new table with an identity column and move all the data over to it, after setting identity insert on.then drop the old table, and rename the new one to the old one's name.
elsasoft.org |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-26 : 02:22:48
|
quote:
Originally posted by dhinasql
is it possible to insert value manually for the identity column?
Yup you can but set identity insert to on before thatSET IDENTITY_INSERT YourTable ONUPDATE YourTAbleSET NewIDColumn=ExistingNonID ColumnSET IDENTITY_INSERT YourTable OFF |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-26 : 02:27:28
|
quote:
Originally posted by dhinasql
hi Visakh,Thanks for your reply, in the existing column i have a data, i want the same data in my table, and suppose i have a lost id is 302, here after i want to add identity for the ID filed so that whenever im inserting a data, it will automatically increase from 302.. i want to achieve this.. could you please explain more...thanks
You can add the data existing to id column as posted before. Once you add all existing data and set identity insert back to off. Any further inserts will ensure value of id column to be automatically incremented. Thus if your last value inserted was 301 it will start with 302,303,.. if the incremental value is 1. So unless any further deletion happens you wont have any gaps in value. Also note that once a deletion happened, you wont be able to recover that id value again. ie if you have 301,302,303 and you delete 302 the next insert will not take 302 but will continue with next chronological order i.e 304. |
|
|
|
|
Adding Identity for existing table