Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unanswered: Making an Existing column an Identity column

    Hi,

    I have a column that is unique that I would like to make into an IDENTITIY column after I insert some data into it.

    I tried

    alter table <table_name>
    alter column <col_name> int Identity (1,1)

    but it fails.


    Ajay

    WORD4LIFE
    (http://www.word4life.com)

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Nope...can't do it...

    If you have a table with a int column and want to change it...you can graphically do it in EM...

    But guess what it's really doing...

    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_Table1a
    	(
    	col1 int NOT NULL IDENTITY (1, 1)
    	)  ON [PRIMARY]
    GO
    SET IDENTITY_INSERT dbo.Tmp_Table1a ON
    GO
    IF EXISTS(SELECT * FROM dbo.Table1a)
    	 EXEC('INSERT INTO dbo.Tmp_Table1a (col1)
    		SELECT col1 FROM dbo.Table1a TABLOCKX')
    GO
    SET IDENTITY_INSERT dbo.Tmp_Table1a OFF
    GO
    DROP TABLE dbo.Table1a
    GO
    EXECUTE sp_rename N'dbo.Tmp_Table1a', N'Table1a', 'OBJECT'
    GO
    GRANT SELECT ON dbo.Table1a TO public  AS dbo
    COMMIT
    Just make sure you don't already have a temp_table...

    What a hack

    M$ is good at them...lots of practice...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Look it up in BOL

    TABLOCKX Use an exclusive lock on a table. This lock prevents others from reading or updating the table and is held until the end of the statement or transaction.
    And the IDENTITY_INSERTS Are inb the correct order...

    btw, sql server itself generated it....look a save sql server change script in EM after you make a change to a table...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •