Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Unanswered: SQL-2005 : set identity_insert <tablename> OFF

    Hi,

    How to drop the identity if the table contains lakh of records using scripts.
    Using the SQL 2005 tool, it's able to drop the identity for the table but i want to do it in scripts.

    I tried with set identity_insert <tablename> OFF.
    still the identity column is ON state only.

    I dont know why..

    I would appreciate if anyone reply for this...
    Thanks

    Regards,
    Sunitha

  2. #2
    Join Date
    Apr 2009
    Posts
    6
    In fact you have set the identity insert ON instead of OFF.

    set identity_insert <tableName> ON

    and again, when you are trying to insert a row using script in that table, if you want to insert the identity column's value, then you must specify the column names.

    insert into <tableName>
    (indentity_column_name, col1, col2, col3)
    values
    (10, 'as', 'as', 'sd', 'we')

  3. #3
    Join Date
    Apr 2009
    Posts
    4
    Thanks Maeenul..
    But I dont think wat U said is I am looking for....
    Wat I need is- To just drop the identity that is already set on my table using script.
    set identity_insert <tablename> OFF is not working.. That is the problem..

    Once again I thankU

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    You cannot permanently remove the IDENTITY property from an existing column. You will have to create a new column or a new table and copy the data into it. There's no other way I'm afraid.

  5. #5
    Join Date
    Apr 2009
    Posts
    4
    Ohh.. Ok..
    thanku for Ur quick reply....

Posting Permissions

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