Jump to content

INSERT Stored Procedure into Table


WynneIT

Recommended Posts

I have a stored procedure that I have scheduled as a job to run nightly at midnight. I want the stored procedure to insert the data into a table when it runs. Do I have to create the table first or is there a way to create the table when the stored procedure runs? My second question is: I then need a job to purge that same table every night at 11pm prior to the stored procedure running again at midnight. Thank you for any help you can provide. I appreciate it.

Here is my Stored Procedure:

USE [TMWSUITE]
GO
/****** Object:  StoredProcedure [dbo].[Wynne_DriverSnapshot]    Script Date: 10/22/2019 6:35:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[Wynne_DriverSnapshot] as


WITH cte (mpp_id, 
mpp_firstname,
mpp_lastname, 
mpp_status,
mpp_fleet,
mpp_terminal) as 
(
Select 
mpp_id, 
mpp_firstname,
mpp_lastname, 
mpp_status,
mpp_fleet,
mpp_terminal
from manpowerprofile 
where mpp_terminationdt >= GETDATE()
),
 cte2 (lgh_number, lgh_startdate, lgh_enddate, lgh_outstatus, rn, lgh_driver1, lgh_class, lgh_class2) as 
(

Select
lgh_number,
lgh_startdate,
lgh_enddate,
lgh_outstatus,
ROW_NUMBER() OVER (PARTITION by lgh_driver1 order by lgh_number desc) as rn,
lgh_driver1,
lgh_class1,
lgh_class2
From legheader

) 
Select 
mpp_id,
mpp_firstname,
mpp_lastname, 
mpp_status,
mpp_fleet,
mpp_terminal,
lgh_number,
lgh_startdate, 
lgh_enddate, 
lgh_outstatus, 
lgh_driver1, 
lgh_class, 
lgh_class2
from cte 
left join cte2 on 
mpp_id = lgh_driver1
where rn = '1' and
mpp_id NOT IN ('UNKNOWN','TESDR','TESDR2')
order by mpp_id

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...