question

isanlu avatar image
isanlu 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
triggerinsertview
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.