Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Cergy
    Posts
    65

    Question Unanswered: How to delete a counter in a field ?

    I want to delete a counter using an SQL query (alter, drop...)

    What could be the SQL query

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by nanou9999
    I want to delete a counter using an SQL query (alter, drop...)

    What could be the SQL query
    Are your trying to reset an identity column or to drop it?
    Plz specify that clearly...

    To reset an identity column use

    DBCC CHECKIDENT ('table_name',RESEED,0)
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Jan 2004
    Location
    Cergy
    Posts
    65
    I want to delete the counter (identity) but keep the column.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Unfortunately, you can't reset the IDENTITY property in Microsoft SQL because of the way that it is implemented. The only safe way to do this is:
    Code:
    ALTER TABLE myTable
       ADD COLUMN new_column INT NOT NULL  -- set type and NULL-ability to taste
    GO
    UPDATE myTable
       SET new_column = old_column
    GO
    ALTER TABLE myTable
       DROP COLUMN old_column
    GO
    EXECUTE sp_rename 'mytable.new_colum', 'mytable.old_column'
    GO
    -PatP

Posting Permissions

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