Gcq1 Gcq1 - 1 month ago 7
SQL Question

Is it possible to run an api connected script on a sql trigger?

I have a table of users that have their id and address. I need to populate a 2nd table with their id and coordinates generated from their address.

What I would like is to generate their coordinates and populate the 2nd table if a new user is added or their address is changed.

Currently, my solution is to export the user list with addresses, use texas A&M's Geoservices application, retrieve their coordinates, import the new excel file columns into their appropriate places within the new table.

I'm aware that I could create an application outside of sql that checks these tables on a daily schedule for differences and update appropriately but I'd really like to automate this within sql so the tables are updated real time.

Answer

You can write code (for example in .NET), and store it as an assembly in your sql-server database. This is called a CLR. You can then run this code directly from the database in the form of stored procedures and functions.

You could write in code a stored procedure to take the data from your first table, look it up, and update / insert into your second table as required. You could call this stored procedure when and were you like, same as any other procedure.

MSDN has an introduction to how to do this.

Filesystemhelper is an example of a clr that does file manipulation - not the code you are looking for, but a good illustration of what I'm talking about.