CODEWITHSUNDEEP
sql-serverstored-procedures
Chris Decker
Chris Decker

Reputation: 478

Simple insert query in stored procedure

I've manipulated my data into the format I need it using a view, and now I have a simple insert query to write that data to a table: 

insert into mytable
select * from myview
where 
(myview.[date] > (select max([date]) from mytable))
and 
(myview.[date] < getdate())

I'd like this to run automatically on a daily basis. I am a bit new to this, but my understanding is that I need to utilize a stored procedure. I am not sure how to execute this code in my stored procedure, though. When I create a new stored procedure, I'm given this template:

CREATE PROCEDURE [dbo].[Update]
    @param1 int = 0,
    @param2 int
AS
    SELECT @param1, @param2
RETURN 0

Could someone help me understand how to bridge from query to stored procedure?

Upvotes: 1

Views: 867

Answers (2)

M.Ali
M.Ali

Reputation: 69524

The procedure definition would be as simple as :

CREATE PROCEDURE dbo.Upload_data
AS
BEGIN
  SET NOCOUNT ON;

    insert into mytable
    select * from myview
    where 
    (myview.[date] > (select max([date]) from mytable))
    and 
    (myview.[date] < getdate())

END

Once you have created the procedure , create a SQL Agent job which calls this procedure every day.

Upvotes: 2

bingo
bingo

Reputation: 2308

Run this and it will create a stored procedure in sql server called the_name_of_your_sproc. 

CREATE PROCEDURE [dbo].[the_name_of_your_sproc]
AS
    insert into mytable
    select * from myview
    where 
    (myview.[date] > (select max([date]) from mytable))
    and 
    (myview.[date] < getdate())

Upvotes: 2

Related Questions