Search code examples
stored-proceduressql-update

Create stored procedure to update table from another server

I have two servers. I copied the table "Response_Master_Incident" on cadarchive server and duplicated it into the ucpdapps2 server and named it "Master_Incident_For_ProQA". When I duplicated it I only selected certain columns to duplicate (since I didn't need all the columns from "Response_Master_Incident").

File Structure in SQL

Now I am trying to create a stored procedure to update the data from "Response_Master_Incident" to "Master_Incident_For_ProQA" pulling over only those select columns.

create procedure UpdateProQATable
as
begin
Select [ID]
      ,[Master_Incident_Number]
      ,[Response_Date]
      ,[Problem]
      ,[MethodOfCallRcvd]
      ,[CallTaking_Performed_By]
      ,[EMD_Used]
      ,[Determinant]
      ,[ProQa_CaseNumber]
      ,[ProQa_CaseNumber_Fire]
      ,[ProQa_CaseNumber_Police]
      ,[MachineName]
into Master_Incident_For_ProQA
from Response_Master_Incident where EMD_Used = '1'
end

When I run this stored procedure I get this error "Msg 208, Level 16, State 1, Procedure UpdateProQATable, Line 4 [Batch Start Line 2] Invalid object name 'Response_Master_Incident'."

How do I resolve this error. And is there a way to have the procedure update the table where the "Response_Date" is a date from yesterday and not all the data from the "Response_Master_Incident" table?

Solution

  • So I figured it out. I needed to go to Databases/Server Objects/Linked Servers and link the server that has the data I needed to pull over to the server that was getting the data.

    After I did that I corrected my script as suggested in the above comment but also added script to delete all the data currently in the table, insert new data, and filtered the new data by the column of EMD_Used and the Response_Date as yesterdays date. After doing this the desired result was met.

    delete from  Master_Incident_For_ProQA
insert into [ucpdapps2].[ProQAUsage].[dbo].[Master_Incident_For_ProQA] 
      ([ID]
      ,[Master_Incident_Number]
      ,[Response_Date]
      ,[Problem]
      ,[MethodOfCallRcvd]
      ,[CallTaking_Performed_By]
      ,[EMD_Used]
      ,[Determinant]
      ,[ProQa_CaseNumber]
      ,[ProQa_CaseNumber_Fire]
      ,[ProQa_CaseNumber_Police]
      ,[MachineName])
Select [ID]
      ,[Master_Incident_Number]
      ,[Response_Date]
      ,[Problem]
      ,[MethodOfCallRcvd]
      ,[CallTaking_Performed_By]
      ,[EMD_Used]
      ,[Determinant]
      ,[ProQa_CaseNumber]
      ,[ProQa_CaseNumber_Fire]
      ,[ProQa_CaseNumber_Police]
      ,[MachineName] FROM [cadarchive].[Reporting_System].[dbo].[Response_Master_Incident] where [EMD_Used] = 1 and [Response_Date] >= DATEADD(day, -1, CONVERT(date, getdate())) AND
      [Response_Date] < CONVERT(date, getdate())