There are a variety of ways to do this, but often you will hit issues with data validity.
If you have our ArcGIS Maplink product (or ArcSquirrel) then loading data in using this into a temporary SQL table is probably best as it will do a reasonable amount of checking.
MapInfo has its own SQL loader tool ("Easy Loader" - find it in the Tool Manager)
If you are just using open source you can use OGR2OGR
ogr2ogr -overwrite -f "MSSQLSpatial" "MSSQL:server=MyServer;database=MyDatabase;Trusted_Connection=yes;" "C:\Temp\MyShapeFile.shp" -a_srs "EPSG:27700"
Once the data is in SQL there can still be issues:
Make sure you specified the correct SRID when the data was loaded (if you didn’t specify it – it may well be 0). You can check this with the SQL: SELECT geom.STSrid from SitesGeom Where geom.STSrid <> 27700
UPDATE foo SET geom = geom.STUnion(geom.STStartPoint());
UPDATE foo SET geom = geom.MakeValid()
SRID not imported
Import geometry data into temp SQL table (e.g. foo) – with tempGeom column
Then try to populate a geometry column from the WKB of the validated column:
ALTER TABLE foo ADD geomFinal geometry;
SET geomFinal = geometry::STGeomFromWKB(geomTemp.STAsBinary(), 27700)