Alter table : identity col

I would like to add Indetntiy property to exitinng column
and would like to start it from 100. Could anybody please
give me the syntaxHi James
You have to drop the table & re-create it afaik.
Here's an example:
set nocount on
go
-- do it in a tran for safety
begin transaction
go
-- set up demo table
create table t1 (
c1 int not null primary key
, c2 char(1) not null
)
go
-- insert a demo row
insert into t1 (c1, c2) values (1, 'a')
go
-- set up a temp table with identity on the column
create table t1_temp (
c1 int not null identity (99, 1) primary key
, c2 char(1) not null
)
go
-- populate the temp table
set identity_insert t1_temp on
insert into t1_temp (c1, c2) select c1, c2 from t1
set identity_insert t1_temp off
go
-- destroy the original table
drop table t1
go
-- rename the temp table to t1
exec sp_rename 't1_temp', 't1'
go
-- insert another row to test
insert into t1 (c2) values ('b')
go
-- check results
select * from t1
go
-- clean up
rollback
go
Things might be a little more complicated if you're using schema binding for
stored procs / views & you might want to flush your proc cache too if you've
got stored procs using the table.
HTH
Regards,
Greg Linwood
SQL Server MVP
"james" <anonymous@.discussions.microsoft.com> wrote in message
news:1413101c3f7e7$c92ea810$a601280a@.phx
.gbl...
> I would like to add Indetntiy property to exitinng column
> and would like to start it from 100. Could anybody please
> give me the syntax|||Greg
I think we can use the same table to add identity property
create table t
(
col int not null primary key,
col2 char(1) not null
)
go
insert into t values (1,'a')
insert into t values (2,'b')
go
alter table t add col1 int identity(1,1)
go
alter table t drop constraint PK__t__41D98783
go
alter table t drop column col
go
EXEC sp_rename 't.col1', 'col', 'COLUMN'
go
select * from t
go
drop table t
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eGs#FA$9DHA.3488@.tk2msftngp13.phx.gbl...
> Hi James
> You have to drop the table & re-create it afaik.
> Here's an example:
> set nocount on
> go
> -- do it in a tran for safety
> begin transaction
> go
> -- set up demo table
> create table t1 (
> c1 int not null primary key
> , c2 char(1) not null
> )
> go
> -- insert a demo row
> insert into t1 (c1, c2) values (1, 'a')
> go
> -- set up a temp table with identity on the column
> create table t1_temp (
> c1 int not null identity (99, 1) primary key
> , c2 char(1) not null
> )
> go
> -- populate the temp table
> set identity_insert t1_temp on
> insert into t1_temp (c1, c2) select c1, c2 from t1
> set identity_insert t1_temp off
> go
> -- destroy the original table
> drop table t1
> go
> -- rename the temp table to t1
> exec sp_rename 't1_temp', 't1'
> go
> -- insert another row to test
> insert into t1 (c2) values ('b')
> go
> -- check results
> select * from t1
> go
> -- clean up
> rollback
> go
> Things might be a little more complicated if you're using schema binding
for
> stored procs / views & you might want to flush your proc cache too if
you've
> got stored procs using the table.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "james" <anonymous@.discussions.microsoft.com> wrote in message
> news:1413101c3f7e7$c92ea810$a601280a@.phx
.gbl...
>|||Greg
Sorry, did not read OP to the end. My example doesnot resolve his problem
because he wants to start from 100.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eGs#FA$9DHA.3488@.tk2msftngp13.phx.gbl...
> Hi James
> You have to drop the table & re-create it afaik.
> Here's an example:
> set nocount on
> go
> -- do it in a tran for safety
> begin transaction
> go
> -- set up demo table
> create table t1 (
> c1 int not null primary key
> , c2 char(1) not null
> )
> go
> -- insert a demo row
> insert into t1 (c1, c2) values (1, 'a')
> go
> -- set up a temp table with identity on the column
> create table t1_temp (
> c1 int not null identity (99, 1) primary key
> , c2 char(1) not null
> )
> go
> -- populate the temp table
> set identity_insert t1_temp on
> insert into t1_temp (c1, c2) select c1, c2 from t1
> set identity_insert t1_temp off
> go
> -- destroy the original table
> drop table t1
> go
> -- rename the temp table to t1
> exec sp_rename 't1_temp', 't1'
> go
> -- insert another row to test
> insert into t1 (c2) values ('b')
> go
> -- check results
> select * from t1
> go
> -- clean up
> rollback
> go
> Things might be a little more complicated if you're using schema binding
for
> stored procs / views & you might want to flush your proc cache too if
you've
> got stored procs using the table.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "james" <anonymous@.discussions.microsoft.com> wrote in message
> news:1413101c3f7e7$c92ea810$a601280a@.phx
.gbl...
>