I'm sure this is a simple problem. The explaining it will be the hardest part. ;-)
#1. On our SQL Server is a database (Gazelle) used by a 3rd party software that we use for customer tracking. We can not make any changes to the structure of that database. We can't add stored procedures or triggers or new tables or fields to existing tables. We can't touch Gazelle.
#2. We have a Youth Employment Program that consists of a set of records from the Gazelle database. We created a SQL View to pull in that set of records.
#3. We need to enter dates to track milestone completions. For that, I've created a Table in the same database as the View that's pulling data from the Gazelle database. Those Table and View are in our own in-house database so we can modify it as needed. I've added new fields for those milestones and fields for completion dates. Staff will enter completion dates as necessary in a ASP.Net Web Application (VB.Net).
Okay. The View, of course, stays up to date with changes to the Gazelle database. But, I've got the Table for entering the milestone dates which needs to be "refreshed" or updated periodically from the View so it has the same set of records.
I think for the web application, I should be able to just put this MERGE query into the Page_Load or a "Refresh" button or both.
MERGE [TempTables].[dbo].[WIAYouthITADates] da
USING [TempTables].[dbo].[vw_WIAYouthITA] vw
ON da.PID = vw.ParticipantId
WHEN MATCHED AND
da.ActualEndDate IS NOT NULL THEN
WHEN MATCHED THEN
da.Client = vw.client,
da.SSN = vw.last4ssn,
da.ProgramStart = vw.ProgramStart,
da.AnticipatedEndDate = vw.AnticipatedEndDate,
da.ActualEndDate = vw.ActualEndDate
WHEN NOT MATCHED BY TARGET THEN
INSERT (PID, Client, SSN, ProgramStart, AnticipatedEndDate, ActualEndDate)
VALUES (vw.ParticipantId, vw.client, vw.last4ssn, vw.ProgramStart, vw.AnticipatedEndDate, vw.ActualEndDate);
Sorry for the long background information...
Here's my Question...
I also have an SSRS Report to show the status of the milestone dates that have been entered into my in-house table.
How can I update the table for the SSRS user(s) if the ASP.Net application hasn't been accessed in a while to update the table?
TLDR: How do you update a Table from a View in SSRS? SSRS displays the Table. The table needs to be "refreshed" or updated from an up to date View.