locked
How to create script RRS feed

  • Question

  • Kindly help me to create this script as a daily scheduled.

    insert into Data_hist
    select * from Data
    where sent_time < DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0) 
    delete from Data where sent_time < DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)

    Wednesday, October 16, 2013 2:27 AM

Answers

  • Follow the below link on to create a SQL Server job,

    http://technet.microsoft.com/en-us/library/ms187910.aspx

    Paste the above in Command box.

    If your intention is to get the start of a day, you can try this as well.

    insert into Data_hist
    select * from Data
    where sent_time < CAST(GETDATE() as date) 
    delete from Data where sent_time <CAST(GETDATE() as date)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by Sarat (SS) Wednesday, October 16, 2013 2:34 AM
    • Proposed as answer by Allen Li - MSFT Thursday, October 17, 2013 5:47 AM
    • Marked as answer by Allen Li - MSFT Thursday, October 24, 2013 7:45 AM
    Wednesday, October 16, 2013 2:34 AM
  • using mangement studio it would a lot easier o configure. Follow the steps in the link shared..

    The syntax to create a job using T-SQL is also present in that link itself and also check this

    http://technet.microsoft.com/en-us/library/ms187358.aspx

    USE msdb;
    GO
    EXEC sp_add_jobstep
        @job_name = N'Weekly Sales Data Backup',
        @step_name = N'Set database to read only',
        @subsystem = N'TSQL',
        @command = N'ALTER DATABASE SALES SET READ_ONLY', 
        @retry_attempts = 5,
        @retry_interval = 5 ;
    GO


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, October 16, 2013 2:44 AM

All replies

  • Follow the below link on to create a SQL Server job,

    http://technet.microsoft.com/en-us/library/ms187910.aspx

    Paste the above in Command box.

    If your intention is to get the start of a day, you can try this as well.

    insert into Data_hist
    select * from Data
    where sent_time < CAST(GETDATE() as date) 
    delete from Data where sent_time <CAST(GETDATE() as date)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by Sarat (SS) Wednesday, October 16, 2013 2:34 AM
    • Proposed as answer by Allen Li - MSFT Thursday, October 17, 2013 5:47 AM
    • Marked as answer by Allen Li - MSFT Thursday, October 24, 2013 7:45 AM
    Wednesday, October 16, 2013 2:34 AM
  • i can't get it - can u give me the script written please if i can run it as a query
    Wednesday, October 16, 2013 2:39 AM
  • using mangement studio it would a lot easier o configure. Follow the steps in the link shared..

    The syntax to create a job using T-SQL is also present in that link itself and also check this

    http://technet.microsoft.com/en-us/library/ms187358.aspx

    USE msdb;
    GO
    EXEC sp_add_jobstep
        @job_name = N'Weekly Sales Data Backup',
        @step_name = N'Set database to read only',
        @subsystem = N'TSQL',
        @command = N'ALTER DATABASE SALES SET READ_ONLY', 
        @retry_attempts = 5,
        @retry_interval = 5 ;
    GO


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, October 16, 2013 2:44 AM
  • Hi,

    Create a Stored Procedure and write your insert query inside procedure and then create a sql servr agent job to schedule your procedure on daily basis.

    Refer below link for creating job in sql server.

    http://technet.microsoft.com/en-us/library/ms190268.aspx

    Please "Marked as Answer" if my post solved your problem.



    Wednesday, October 16, 2013 4:15 AM
  • Hi

    In addition to solution provided by Shweta Vijayvargiya you can also check this link(Step by step process with screens): http://blogs.msdn.com/b/sqlagent/archive/2010/10/14/creating-a-regularly-scheduled-job.aspx


    Do not Forget to Vote as Answer/Helpful, please. It encourages us to help you.

    Wednesday, October 16, 2013 4:30 AM