Jump to content
WynneIT

INSERT Stored Procedure into Table

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

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...