Results 1 to 5 of 5

Thread: Truncate table

  1. #1
    Join Date
    May 2004
    Posts
    36

    Talking Unanswered: Truncate table

    what is the best way to truncate/delete records from a table at a certain time of the day...... i'm sure this is a simple ?, but i'm still learning sql.


    thanks,
    eddie

  2. #2
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Cool Truncate table

    Use Truncate command with datetime function:

    Example:
    delete FROM dbo.Table WHERE DATE_TIME <=dateadd(day, -180, getdate())

    This delect records from a table leaving a 6 month period. It's based on the datetime field in the table and the getdate system function.

  3. #3
    Join Date
    Feb 2004
    Posts
    492
    You could create a job which is scheduled a time X daily or weekly that executes the delete or truncate. A truncate deletes all records from the tables whereas with delete you can be more specific which records to delete.
    You might want to consider other users doing requests and updates though.

  4. #4
    Join Date
    Jan 2004
    Posts
    164

    Truncate table

    You can do this in two steps. First, create a stored procedure.

    Example:

    CREATE PROCEDURE sp_Truncate AS

    truncate table (whatevertable)

    ************************************************** *****

    Second, create a job that will run that stored procedure by the schedule you assigned it.

    You creat a job by going to Management forlder in the console. Then click on SQL Server Agents. Then go to Jobs. Rignt click where the jobs would be listed and select new job.

    Then it will bring up a new job properties box.

    In the General Tab, fill in name and the description.

    In the Steps tab, Click on New for a new step. This will open up a Step Properties box. Name the Step. Select the database, and in the command box you will put exec sp_Truncate or whatever yo named your stored procedure. Click ok.

    Click on Schedule tab, and select new schedule. This brings up a Schedules property box. The recurring radio box will be marked. Select the Change button, and scheule it the way you want it. It can run monthly, weekly, or daily.

    That is how you would schedule a job.

  5. #5
    Join Date
    May 2004
    Posts
    36
    i created a sp and scheduled it. many thanks to everyone who posted a response for all of ur help.

Posting Permissions

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