asked
Inserting new records from a view
I have a core application that I am not allowed to make object changes to, that means no new tables, views or stored procedures. I can however, query the data from another Server or Database. I have created on another linked server a view that returns two columns from a single table. The question is how do I insert any new records that are added to the source table and appear in the view to a target table. I cannot create an Insert trigger on the source table and an Instead of trigger on the view does not work since I am not inserting data into the view. All I have now is a Scheduled task that insert any records from source not found in target but it is not "live" data. Thanks
Have you looked at the OUTPUT clause option --
https://msdn.microsoft.com/en-us/library/ms177564.aspx? Just brain storming. If you have access to the DML statements you might be able to get creative and solve your problem.
The best thing I can suggest is that you schedule your job to run once a minute and use the [MERGE statement][1] to capture changes based on the key structure of the tables. There just aren't that many choices you have if you're attempting to monitor something remotely with limited access. [1]:
https://msdn.microsoft.com/en-us/library/bb510625.aspx
16 People are following this question.
