How to load geometry point tables in sql spatial
Its very clunky but you need to use stpointfromtext
geometry::STPointFromText('POINT(xxxxx yyyyyy)', SRID)
where x andf y are the co ordinate values and SRID is the grid identifier for the projection you are using. Wrap it all up with a cursor like the following example
https://support.esri.com/en/technical-article/000012317