Post reply

Create procedure to delete records and reset identity column.

  • October 19, 2005 at 4:26 am

    #91835

    Hi,

    I need a help. I want to schedule a job, which will execute a stored procedure each sunday. I want to delete some records based on a criterion from the database and reset the identity column.

    kindly help me, how could i do this.

    Thanks and Regards

    Deepak

  • October 19, 2005 at 5:45 am

    #598183

    DBCC CHECKIDENT ('your table',RESEED ,your new identity value)

  • October 19, 2005 at 6:27 am

    #598190

    Hi!!!

    U can do one thing here Go to EM and Delete that ID column by right clicking to it and again right click on the next column where u want to insert that ID column and set it for IDENTITY...

     

    Regards,

    Papillon

  • October 19, 2005 at 7:10 am

    #598214

    Create the procedure to do the following

    DELETE FROM

    WHERE ....

    DECLARE @newseed int,@sql nvarchar(100)

    SELECT @newseed=ISNULL(MAX([ID]),0) FROM

    SET @sql = N'DBCC CHECKIDENT (

    ,RESEED,' + CAST(@newseed as nvarchar) + ')'

    EXEC sp_executesql @sql

    Create a job the run the procedure (or the sql above if you do not want to create the proc)

     

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply