Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    28

    Unanswered: Identity column without script??

    I have a database that is already created and I would like to change all the row id columns (which are currently bigint fields) and turn them into autonumbering Identity fields. Is there a way to do this through the enterprise manager or do I need to recreate all tables in the database using a script that creates identity columns in CREATE_TABLE and then import existing data into it?

    Thanks in advance!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(Col1 bigint, col2 char(2))
    GO
    
    INSERT INTO myTable99(Col1, Col2)
    SELECT 1,'a' UNION ALL
    SELECT 2,'b' UNION ALL
    SELECT 3,'c'
    GO
    
    -- This is what EM will Do
    
    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_myTable99
    	(
    	Col1 int NOT NULL IDENTITY (1, 1),
    	col2 char(2) NULL
    	)  ON [PRIMARY]
    GO
    SET IDENTITY_INSERT dbo.Tmp_myTable99 ON
    GO
    IF EXISTS(SELECT * FROM dbo.myTable99)
    	 EXEC('INSERT INTO dbo.Tmp_myTable99 (Col1, col2)
    		SELECT CONVERT(int, Col1), col2 FROM dbo.myTable99 TABLOCKX')
    GO
    SET IDENTITY_INSERT dbo.Tmp_myTable99 OFF
    GO
    DROP TABLE dbo.myTable99
    GO
    EXECUTE sp_rename N'dbo.Tmp_myTable99', N'myTable99', 'OBJECT'
    GO
    COMMIT
    GO
    
    SELECT * FROM myTable99
    GO
    
    sp_help myTable99
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    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
    Sep 2004
    Posts
    28
    Yes, but I wanted to do it through the enterprise manager (the database is already set up and populated, but not in production yet.). I was hoping maybe all I had to do was enter a formula in the design view for each table, and then do inserts in code without having to enter that field in my inserts.

    Can this be done?

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    if you are asking if you can add an identity to a column after the table has been created and the data exists, the answer is yes

    open the enterprise manager
    right click the table that you want to modify
    Right click the table and select Design Table
    in the grid at the top choose the column you want to add the identity to
    and at the bottom select the following properties

    identity = yes
    seed = this is the initial value which in the case of existing data is the highest value placed in the column. os for example if your last value in the col was 234 the identityseed would be 234
    increment the number that you want to increase the identity by. usually 1

    when you insert another row to this table the identity will add 1 to the seed and give you 235 as your next value.

    is this what you wanted?

  5. #5
    Join Date
    Sep 2004
    Posts
    28
    That's exactly what I wanted! Thanks!!

Posting Permissions

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