Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105

    Unanswered: T-SQL way to reset Primary Key - need for DTS

    I have a DTS package that deletes all the records in a table and repopulates from an ACCESS table. All's well except the primary key of the target table doesn't go back to 1.

    What's the syntax in T-SQL to accomplish that?

    I imagine it's dropping the column and adding it back - but since it's a primary key field, regular drop/add syntax isn't working for me.

    Any help here is appreciated.

    Thanks,

    Peter

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by Pdiotte
    I have a DTS package that deletes all the records in a table and repopulates from an ACCESS table. All's well except the primary key of the target table doesn't go back to 1.

    What's the syntax in T-SQL to accomplish that?

    I imagine it's dropping the column and adding it back - but since it's a primary key field, regular drop/add syntax isn't working for me.

    Any help here is appreciated.

    Thanks,

    Peter
    is it an identity column?

    SET IDENTITY_INSERT <table_name> ON

    do your business

    SET IDENTITY_INSERT <table_name> OFF
    “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
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105
    Sean:

    Thanks - huge help!

    The only tweak from your suggestion was to set the identity OFF first, then do business, then back ON.

    Thanks

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Your "tweak" makes no sense.

    Setting IDENTITY_INSERT ON allows the T-SQL to insert values into the named identity column. Setting IDENTITY_INSERT OFF disables that feature. Lookup SET IDENTITY_INSERT in BOL.

    I was going to suggest you look up DBCC CHECKIDENT in BOL as a method of resetting the seed value of the identity column before reinserting data after you delete from or truncate the table.

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105
    tomh:

    Here's my code:

    SET IDENTITY_INSERT gptestout OFF
    delete gptestout
    SET IDENTITY_INSERT gptestout ON

    I then continue a DTS script that brings data from Access to gptestout.

    I populate the keys with values from the Access table. With the above, the keys remain the same as in Access. Without the above, the keys started from the last highest value prior to 'delete gptestout'

Posting Permissions

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