WynneIT Posted October 22, 2019 Share Posted October 22, 2019 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now