Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2005
    Posts
    71

    Unanswered: Alter a column to be the table identity column

    i have a table
    table1
    column1 int not null
    column2 char not nul
    column3 char

    i want to script a change for table1 to alter column1 to be the table identity column. not primary.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you can not. You must create a new table with an identity column already defined and copy your data from the old to the new, drop your old table and rename your new table
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Mar 2005
    Posts
    71
    Just a curious question then why is that i can use the enterprise manager to do it?
    the tables in question have data in them which is why i dont want to drop them.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quote Originally Posted by hicpics
    Just a curious question then why is that i can use the enterprise manager to do it?
    the tables in question have data in them which is why i dont want to drop them.
    Care to guess what Enterprise Mangler is about to do in the background?

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by MCrowley
    Care to guess what Enterprise Mangler is about to do in the background?
    it is really curious that the enterprise mangler does use to do this operation. I had a whole of tables to convert to identity once and I traced the operation that EM does and there is a lot of weird stuff in their that does not pan out in the QA.

    as for dudes question. you need to do a an

    INSERT INTO MyNewTable
    SELECT ... FROM MyOldTable

    before you drop the old table.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Mar 2005
    Posts
    71
    Well since it was the client that messed the tables up by importing them into another db then importing them back without selecting all objects, i showed them how to fix them using the EM and said to get after it. (1242 tables in all which is why i was trying to find a way of scripting it). i did use another clients db to create all the indexes and sent to them to use.
    anyways thanks for all the time and help.

Posting Permissions

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