alter column to an identity column

  • How can I alter a table column into an Identity column using sql transact??

    I Tried this way but SQL Server returns an error.

    alter table orders

    alter column id_order int not null Identity(1,1)

    10x.

    Heber Zachi

  • You cannot have nulls in the column. If you are receiving an error there are probably nulls.

    Scripting from enterprise manager changing a column to Identity, it creates a table called Tmp_youtablename with identity

    Then it moves the data to tmp_yourtable from your table

    then it drops yourtablename

    then it renames to yourtablename from tmp_yourtablename.

    Use the same way it works every time.

    Code:

    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_Orders

    (

    id_Order int NOT NULL IDENTITY (1, 1),

    stuffy char(10) NULL

    ) ON [PRIMARY]

    GO

    SET IDENTITY_INSERT dbo.Tmp_Orders ON

    GO

    IF EXISTS(SELECT * FROM dbo.Orders)

    EXEC('INSERT INTO dbo.Tmp_Orders (id_Order, stuffy)

    SELECT id_Order, stuffy FROM dbo.Orders TABLOCKX')

    GO

    SET IDENTITY_INSERT dbo.Tmp_Orders OFF

    GO

    DROP TABLE dbo.Orders

    GO

    EXECUTE sp_rename N'dbo.Tmp_Orders', N'Orders', 'OBJECT'

    GO

    COMMIT

  • not sure if it possible to change a column into identity column after it has been created.  BOL does not state this is explicitly though; all references within BOL to adding an identity column applied to new columns only.

    /* --- cut here --- */

    use tempdb

    go

    begin tran

    create table orders(

    id_order int

    )

    go

    alter table orders alter column id_order int identity(1,1)

    /* error here:

    Server: Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'identity'.

    */

    go

    alter table orders add id_order2 int identity(1,1)

    /* can only create identity for new columns */

    go

    select * from orders

    rollback;

    /* --- cut here --- */

    Billy

  • Run this:

     

    alter table orders

    drop column id_order 

    alter table orders

    alter column id_order int not null Identity(1,1)

     

  • Sorry, too fast reply:

    alter table orders

    drop column id_order 

    alter table orders

    add  id_order int not null Identity(1,1)

  • Markus Veretin (7/14/2004)


    Sorry, too fast reply:

    alter table orders

    drop columnid_order

    alter table orders

    addid_orderint not null Identity(1,1)

    If you just drop the existing column and add a new column with the same name but define it as identity, you’ll might modify the value of the primary key for some of the records in the table. There is no guarantee that the new column will get the same values as the old columns.

    Since there is no way to modify the column itself to be an identity column, you’ll have to create a new table with an identity column, insert the data from the old table into the new table (with set identity_insert on you’ll get the same values in the new column as you had in the old column), rename or drop the original table (It is better to rename it first, so you’ll be able to roll back the modification if something goes wrong), rename the new table to the original table’s name, recreate the foreign keys that referenced the original table and run dbcc checkident to initialize the identity’s seed to the next number.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • alter column to identity is very easy in ms sql server 2005

    just right click on column you want to alter and click on design and change identity to YES

    Enjoy!!!!!

  • pawantiwari.3987 (3/18/2011)


    alter column to identity is very easy in ms sql server 2005

    just right click on column you want to alter and click on design and change identity to YES

    Enjoy!!!!!

    Okay now try to get that into a script to be run on a production server.

    FYI this can also be done in SQL2000 Enterprise Manager. But as that and SSMS doesnt do anything magically. They are still restricted to what SQL can do and what can be scripted. Now the script that gets created in both 2000 and 2005 is that a new table is created and all data is copied to it. The old table gets deleted and the new one gets renamed.

    There is more to SQL then point and click (but can be useful for getting a script easy :-D)

    This is part off the script that gets created (on a little test table i created just for the occation)

    CREATE TABLE dbo.Tmp_tbl

    (

    i int NOT NULL IDENTITY (1, 1)

    ) ON [PRIMARY]

    GO

    SET IDENTITY_INSERT dbo.Tmp_tbl ON

    GO

    IF EXISTS(SELECT * FROM dbo.tbl)

    EXEC('INSERT INTO dbo.Tmp_tbl (i)

    SELECT i FROM dbo.tbl TABLOCKX')

    GO

    SET IDENTITY_INSERT dbo.Tmp_tbl OFF

    GO

    DROP TABLE dbo.tbl

    GO

    EXECUTE sp_rename N'dbo.Tmp_tbl', N'tbl', 'OBJECT'

    GO

    Oh and this would also indicate that you cant add the identity property to an existing column.

    /T

  • or just do this

    procedure [dbo].[MakeIndendity](@identityField varchar(50), @fieldsDeclare varchar(255),@fieldsSelect varchar(255),@tableName varchar(50))

    as

    Begin

    EXEC('CREATE TABLE Tmp_tbl(' + @fieldsDeclare + ')')

    SET IDENTITY_INSERT Tmp_tbl ON

    EXEC('INSERT INTO Tmp_tbl (' + @fieldsSelect + ') SELECT ' + @fieldsSelect + ' FROM ' + @tableName + ' TABLOCKX')

    SET IDENTITY_INSERT Tmp_tbl OFF

    EXEC('DROP TABLE ' + @tableName)

    EXECUTE sp_rename N'Tmp_tbl', N@tableName, 'OBJECT'

    EXEC('ALTER TABLE [dbo].' + @tableName + ' WITH NOCHECK ADD CONSTRAINT [PK_' + @tableName + '] PRIMARY KEY CLUSTERED (' + @identityField + ') WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY];')

    End

    be better to just pass the table and the column and figure loop the fields itself but i was too lazy i guess

    Michael Evanchik

    Microsoft MVP

    http://www.before0day.com

    http://www.MikeEvanchik.com

  • just to fix a few bugs

    ALTER procedure [dbo].[MakeIndendity](@identityField varchar(50), @fieldsDeclare text,@fieldsSelect text,@tableName varchar(50))

    as

    Begin

    EXEC('CREATE TABLE Tmp_tbl(' + @fieldsDeclare + ')')

    SET IDENTITY_INSERT Tmp_tbl ON

    EXEC('INSERT INTO Tmp_tbl (' + @fieldsSelect + ') SELECT ' + @fieldsSelect + ' FROM ' + @tableName + ' TABLOCKX')

    SET IDENTITY_INSERT Tmp_tbl OFF

    EXEC('DROP TABLE ' + @tableName)

    EXEC('BEGIN TRY while (select 1 from ' + @tableName + ') = 1 begin if exists(select * from ' + @tableName + ') break else continue end END TRY BEGIN CATCH END CATCH ')

    EXECUTE sp_rename N'Tmp_tbl', @tableName, 'OBJECT'

    EXEC('ALTER TABLE ' + @tableName + ' WITH NOCHECK ADD CONSTRAINT [PK_' + @tableName + '] PRIMARY KEY CLUSTERED (' + @identityField + ') WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]')

    End

    Michael Evanchik

    Microsoft MVP

    http://www.before0day.com

    http://www.MikeEvanchik.com

Viewing 10 posts - 1 through 9 (of 9 total)

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