Changing between Identity type and Int type

What is the TSQL command to chanage a column of int type to identity type
and vice versa?
Thanks in advance!
KMYou can't add the IDENTITY property to an existing table - you need to
create a new table with the IDENTITY column. If you have existing data, you
can create it with a different name, INSERT the existing data, drop the
existing table, then rename the new table. Enterprise Manager will do this
for you if you add the property in the table designer.
HTH. Ryan
"krygim" <krygim@.hotmail.com> wrote in message
news:OccfMRAIGHA.2012@.TK2MSFTNGP14.phx.gbl...
> What is the TSQL command to chanage a column of int type to identity type
> and vice versa?
> Thanks in advance!
> KM
>|||To add on, if your table is having millions of data do go for Enterprise
Manager, you can mess up your life doing so. It will remain in a hanged
situation. So better do it manual ways like creating a new table and renamin
g
droping the original one.
Thanks,
Sree
"Ryan" wrote:

> You can't add the IDENTITY property to an existing table - you need to
> create a new table with the IDENTITY column. If you have existing data, yo
u
> can create it with a different name, INSERT the existing data, drop the
> existing table, then rename the new table. Enterprise Manager will do this
> for you if you add the property in the table designer.
> --
> HTH. Ryan
> "krygim" <krygim@.hotmail.com> wrote in message
> news:OccfMRAIGHA.2012@.TK2MSFTNGP14.phx.gbl...
>
>|||But when I insert the existing data into the new table with the identity
column, how can I make sure that the keys are not changed?
(I am thinking of existing data having integer keys and a lot of unused
numbers due to deletions.)
Ryan wrote:

> You can't add the IDENTITY property to an existing table - you need to
> create a new table with the IDENTITY column. If you have existing data, yo
u
> can create it with a different name, INSERT the existing data, drop the
> existing table, then rename the new table. Enterprise Manager will do this
> for you if you add the property in the table designer.
>|||> You can't add the IDENTITY property to an existing table -
No ,you CAN
create table #t
(
col1 char(1)
)
insert into #t values ('a')
insert into #t values ('b')
insert into #t values ('c')
alter table #t add col2 int identity(1,1)
select * from #t
Actually you cannot EDIT/ALTER an IDENTITY property
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:es%23Y6YAIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> You can't add the IDENTITY property to an existing table - you need to
> create a new table with the IDENTITY column. If you have existing data,
> you
> can create it with a different name, INSERT the existing data, drop the
> existing table, then rename the new table. Enterprise Manager will do this
> for you if you add the property in the table designer.
> --
> HTH. Ryan
> "krygim" <krygim@.hotmail.com> wrote in message
> news:OccfMRAIGHA.2012@.TK2MSFTNGP14.phx.gbl...
>|||My understanding of the question was to add the identity property to an
existing INT column, admitidly my opening sentance should have read :-
You can't add the IDENTITY property to an existing column
rather than
You can't add the IDENTITY property to an existing table -
Apologies for any mis-understanding. Either way KM now has an answer to his
question.
HTH. Ryan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eiljmwAIGHA.1388@.TK2MSFTNGP11.phx.gbl...
> No ,you CAN
> create table #t
> (
> col1 char(1)
> )
> insert into #t values ('a')
> insert into #t values ('b')
> insert into #t values ('c')
> alter table #t add col2 int identity(1,1)
> select * from #t
>
> Actually you cannot EDIT/ALTER an IDENTITY property
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:es%23Y6YAIGHA.2628@.TK2MSFTNGP15.phx.gbl...
>|||Ryan,
In the Enterprise Manager, I can create an int column with non-contiguous
(and even duplicated) values in different records, e.g., 1, 31, 1, 7 in 4
records. (Please note that the values are not in ascending order and two of
them are duplicated.) Then later, I can change the column to an identity
column without any value change. Thus I am sure that rather than dropping
and recreating the column, the Enterprise Manager is doing it in another
way. Please correct me know if I am wrong.
KM
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:es%23Y6YAIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> You can't add the IDENTITY property to an existing table - you need to
> create a new table with the IDENTITY column. If you have existing data,
you
> can create it with a different name, INSERT the existing data, drop the
> existing table, then rename the new table. Enterprise Manager will do this
> for you if you add the property in the table designer.
> --
> HTH. Ryan
> "krygim" <krygim@.hotmail.com> wrote in message
> news:OccfMRAIGHA.2012@.TK2MSFTNGP14.phx.gbl...
type
>|||Hi Ferdinand,
To your question:
You should Set identity_insert on on the new table with the identity
before you insert the data.
The Set will last for the session or until you set identity_insert off.
Example:
Create table #source (cola int, colb int)
insert into #source values (1,2)
insert into #source values (3,4)
Create table #dest (cola int identity, colb int)
set identity_insert #dest on
Insert into #dest (cola,colb)
Select cola, colb
From #source
Select *
from #dest
-- drop table #dest
-- drop table #source
"Ferdinand Zaubzer" wrote:

> But when I insert the existing data into the new table with the identity
> column, how can I make sure that the keys are not changed?
> (I am thinking of existing data having integer keys and a lot of unused
> numbers due to deletions.)
>
> Ryan wrote:
>
>|||Run the SQL Profiler as the EM makes the change. You'll be amazed.
ML
http://milambda.blogspot.com/|||> Thus I am sure that rather than dropping
> and recreating the column, the Enterprise Manager is doing it in another
> way. Please correct me know if I am wrong.
>
Enterprise Manager create a new table, loads data from the old table and
them drops the old table to implement this change. You can verify this with
a Profiler trace or by clicking the 'save change script' button. The end
result is as if the identity property was simply added to the column but the
actual technique used is more involved.
Hope this helps.
Dan Guzman
SQL Server MVP
"Krygim" <krygim@.hotmail.com> wrote in message
news:OrPh5wBIGHA.2912@.tk2msftngp13.phx.gbl...
> Ryan,
> In the Enterprise Manager, I can create an int column with non-contiguous
> (and even duplicated) values in different records, e.g., 1, 31, 1, 7 in 4
> records. (Please note that the values are not in ascending order and two
> of
> them are duplicated.) Then later, I can change the column to an identity
> column without any value change. Thus I am sure that rather than dropping
> and recreating the column, the Enterprise Manager is doing it in another
> way. Please correct me know if I am wrong.
> KM
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:es%23Y6YAIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> you
> type
>