alter a column to Identity

  • How to alter a exist column which datatype is decimal(19,0)to identity

    when i am using following query it is giving error

    alter table ximport alter column autoid decimal(19,0) identity(1,1) not null

  • What error do you get?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi,

    I saved the change script after adding manualy the IDENTITY property to the autoid column in Enterprise Manager. This is the generated script:

    BEGIN TRANSACTION

    SET QUOTED_IDENTIFIER ON

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    SET ARITHABORT ON

    SET NUMERIC_ROUNDABORT OFF

    SET CONCAT_NULL_YIELDS_NULL ON

    SET ANSI_NULLS ON

    SET ANSI_PADDING ON

    SET ANSI_WARNINGS ON

    COMMIT

    BEGIN TRANSACTION

    CREATE TABLE dbo.Tmp_ximport

    (

    autoid decimal(19, 0) NOT NULL IDENTITY (1, 1)

    ) ON [PRIMARY]

    GO

    SET IDENTITY_INSERT dbo.Tmp_ximport ON

    GO

    IF EXISTS(SELECT * FROM dbo.ximport)

    EXEC('INSERT INTO dbo.Tmp_ximport (autoid)

    SELECT autoid FROM dbo.ximport TABLOCKX')

    GO

    SET IDENTITY_INSERT dbo.Tmp_ximport OFF

    GO

    DROP TABLE dbo.ximport

    GO

    EXECUTE sp_rename N'dbo.Tmp_ximport', N'ximport', 'OBJECT'

    GO

    COMMIT

    Regards,

    Oana.

  • From Eneterprise Manager you can alter the table column as identity.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply