Can I insert via a view, and convert data types?

user_385695 0
Viewed 101

I want to insert into a table, but the tool I am using does not support the data type. Specifically, I want to use Azure Data Factory to insert a geometry data type into a Microsoft SQL Server table.

Is there a way of performing this insert using, for example, a WKT string such a 'POINT(100 100)', and have this automatically converted within SQL Server to the geometry data type? Could I do this via a view or some kind of stored procedure or trigger?

azure-data-factory-2 azure-data-factory azure
1 Answers

I can achieve what I want with a view and a trigger:

create table [phil].[test_geo]
(   [uid] NUMERIC(12,0), 
    [coords] geometry
)

create view [phil].[test_geo_string] as
select [uid], [coords].STAsText()
from [phil].[test_geo]

create trigger [INSTEADOF_TR_I_test_geo_string]
ON [phil].[test_geo_string]
INSTEAD OF INSERT AS
BEGIN
  INSERT INTO [phil].[test_geo] (  [uid], [coords] )
    SELECT  I.[uid], geometry::STGeomFromText(I.[coords_string], 27700) 
    FROM INSERTED I
END

Apologies for any typos, as I am working over an air-gap.

user_3856950
Related