Changing Identity column to Identity not for replication?

Changing Identity column to Identity not for replication?

Post by Robert Hildebran » Sun, 20 Apr 2003 03:04:42



I have an identity column defined on a table and need to
change it to use the NOT FOR REPLICATION option.  I've
read and re-read the ALTER TABLE documentation and i'm
not clear on the syntax, or whether or not this is
possible.  

Any suggestions would be greatly appreciated!!!!

Thanks,

Robert

 
 
 

Changing Identity column to Identity not for replication?

Post by Bob Barrow » Sun, 20 Apr 2003 03:20:12



> I have an identity column defined on a table and need to
> change it to use the NOT FOR REPLICATION option.  I've
> read and re-read the ALTER TABLE documentation and i'm
> not clear on the syntax, or whether or not this is
> possible.

You can't use ALTER TABLE for this. Here's a demo:

CREATE TABLE dbo.AlterIdentityDemo
(
    col1 int     IDENTITY,
    col2 char(1) NOT NULL
)
go
insert AlterIdentityDemo(col2)
select 'a'
union all
select 'b'
union all
select 'c'

go
EXEC sp_rename
'dbo.AlterIdentityDemo','AlterIdentityDemo_04182003181434000',OBJECT
go
CREATE TABLE dbo.AlterIdentityDemo
(
    col1 int     IDENTITY NOT FOR REPLICATION,
    col2 char(1) NOT NULL
)
go
SET IDENTITY_INSERT dbo.AlterIdentityDemo ON
go
INSERT INTO dbo.AlterIdentityDemo(
                          col1,
                          col2
                         )
                   SELECT
                          col1,
                          col2
                     FROM dbo.AlterIdentityDemo_04182003181434000
go
SET IDENTITY_INSERT dbo.AlterIdentityDemo OFF
go

HTH,
Bob Barrows

 
 
 

Changing Identity column to Identity not for replication?

Post by John Bel » Sun, 20 Apr 2003 03:40:32


Hi

Although you can add an identity column with the alter table, you can't
alter a column to be an identity even if it was already!  This is as the
syntax described in BOL.

John

Quote:> I have an identity column defined on a table and need to
> change it to use the NOT FOR REPLICATION option.  I've
> read and re-read the ALTER TABLE documentation and i'm
> not clear on the syntax, or whether or not this is
> possible.

> Any suggestions would be greatly appreciated!!!!

> Thanks,

> Robert

 
 
 

Changing Identity column to Identity not for replication?

Post by Robert Hildebran » Sun, 20 Apr 2003 04:38:22


Thanks for the info everybody!