Alter column to Identity

Is it possible to alter the column of integer type as indentity field
through query?
MadhivananSort of. You have to create a new table, insert the rows from the old
table, then drop the original table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1124967989.632225.140630@.o13g2000cwo.googlegroups.com...
Is it possible to alter the column of integer type as indentity field
through query?
Madhivanan|||Well
If it is possible to do this from EnterPrise Manager, then I wonder why
there is no query to do this?
Madhivanan|||Hi, Tom
I think , there is no need to create a new table , we can just add a new
column.
create table #t
(
col int
)
insert into #t values (10)
insert into #t values (100)
go
alter table #t add col1 int identity(1,1)
go
select * from #t
go
alter table #t drop column col
go
select * from #t
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ucmrsVWqFHA.3540@.TK2MSFTNGP14.phx.gbl...
> Sort of. You have to create a new table, insert the rows from the old
> table, then drop the original table.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Madhivanan" <madhivanan2001@.gmail.com> wrote in message
> news:1124967989.632225.140630@.o13g2000cwo.googlegroups.com...
> Is it possible to alter the column of integer type as indentity field
> through query?
> Madhivanan
>|||Enterprise Manager actually does it the way I described.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1124968780.394543.215750@.o13g2000cwo.googlegroups.com...
Well
If it is possible to do this from EnterPrise Manager, then I wonder why
there is no query to do this?
Madhivanan|||True, though the ordinal positions have to change. Also, the original
values aren't preserved.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eU1%23afWqFHA.3192@.TK2MSFTNGP10.phx.gbl...
Hi, Tom
I think , there is no need to create a new table , we can just add a new
column.
create table #t
(
col int
)
insert into #t values (10)
insert into #t values (100)
go
alter table #t add col1 int identity(1,1)
go
select * from #t
go
alter table #t drop column col
go
select * from #t
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ucmrsVWqFHA.3540@.TK2MSFTNGP14.phx.gbl...
> Sort of. You have to create a new table, insert the rows from the old
> table, then drop the original table.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Madhivanan" <madhivanan2001@.gmail.com> wrote in message
> news:1124967989.632225.140630@.o13g2000cwo.googlegroups.com...
> Is it possible to alter the column of integer type as indentity field
> through query?
> Madhivanan
>|||In your query
1.create a temp table
2.insert all rows to the tepm table
3.drop your table
4.create it with an IDENTITY property on that column
5.Set IDENTITY_INSERT yuor_table_name ON
6.insert all rows into your table
7.Set IDENTITY_INSERT yuor_table_name OFF
If the table has dependencies then it is more complicated...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OJAVmkWqFHA.3064@.TK2MSFTNGP15.phx.gbl...
> True, though the ordinal positions have to change. Also, the original
> values aren't preserved.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eU1%23afWqFHA.3192@.TK2MSFTNGP10.phx.gbl...
> Hi, Tom
> I think , there is no need to create a new table , we can just add a new
> column.
> create table #t
> (
> col int
> )
> insert into #t values (10)
> insert into #t values (100)
> go
> alter table #t add col1 int identity(1,1)
> go
> select * from #t
> go
> alter table #t drop column col
> go
> select * from #t
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ucmrsVWqFHA.3540@.TK2MSFTNGP14.phx.gbl...
>|||> If it is possible to do this from EnterPrise Manager, then I wonder why
> there is no query to do this?
Why don't you start up profiler, and watch what happens when you do this in
Enterprise Manager. You will see that there is no query to do this, the
system actually does what Tom described.|||Try this in EM:
Open a table in desig mode
Change a column to Identity
Before saving the change, lick on the icon Save Change Script
You will see the script EM generates to make the change.
Try to choose a table that has at lease one foreign key reference.
On one of my base tables, this generates 285 lines of code.
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1124968780.394543.215750@.o13g2000cwo.googlegroups.com...
> Well
> If it is possible to do this from EnterPrise Manager, then I wonder why
> there is no query to do this?
> Madhivanan
>|||Thanks Raymond
Now I clearly understand
Madhivanan