Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    83

    Unanswered: Insert or Change Identity

    How can I insert or drop identity for a column from the query analyser?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I don't think you can...

    You can create a new column, insert the data from it, drop the column, add a new column with the old name, and move the data back...don't know if you can rename a coulmn..

    Gotta look more closley at ALTER..

    anyway, I was hacking around with this

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99 (Col1 int IDENTITY, Col2 Char(1))
    GO
    
    INSERT INTO myTable99 (Col2) SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'
    GO
    
    ALTER TABLE myTable99 ALTER Column Col1 int 
    GO
    
    INSERT INTO myTable99 (Col2) SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'
    GO
    
    SELECT * FROM myTable99
    GO
    
    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
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well, I gotta take that back...you can change it in Enterprise manager....so you should be able to do an ALTER...just couldn't see it...
    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.

  4. #4
    Join Date
    Oct 2003
    Posts
    83
    Originally posted by Brett Kaiser
    Well, I gotta take that back...you can change it in Enterprise manager....so you should be able to do an ALTER...just couldn't see it...
    I know it is possible in Enterprise Manager but I like to do it through Query Analyser.But the question is how?

  5. #5
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    You can add a new column which has the identity property.
    You can drop the column.

    Don't think you can remove the identity property though.
    e-m will probably create a new table and copy the data - it tends to do that even if it doesn't need to.

    As Brett says you can create a new column, copy the data and drop the old.

Posting Permissions

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