I feel I may be in a little over my head with this one and could do with some advice.
Currrently I am running a database for searching through technical data for a system and returning this information via a perl script from a stored procedure. This information is then used by another company for their front end.
I have now been asked to link into a customer database to refine the information that is being returned and have become stuck.
Firstly the data being retuned is from a web service which I have very little experiance of so am unsure how to handle the data.
Secondly after the data has been compared I then need to create a list of items that were removed from the technical data due to customer history then create a reference so that the record can be reproduced at a later date.
Now if it was all in SQL I could handle it fine as I could return the data from the Web Service into a temporary table and perform the comparisons I need, generate the code and then send the information as I do now.
What I am struggling with is getting the data into SQL, searched online and found examples using CLR but there are several methods and I am unsure which one to use. One I tried didn't seem to work.
The option would be to create my own web service, return the data as I do now to that then request the data from the other web service and then compare the two data sets.
Ideally I would like to keep it all in SQL as I only need to worry about one thing instead of three but are there any things I should be aware of doing this? Also what is the best way to do this, currently the web service is returning the data as a long string which then needs to be inserted into a table, can this be done via SQL?