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:

Incorrect SRID

• 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

Invalid ring

• You may also have invalid ring geometries – a good post on this is http://blogs.msdn.com/b/edkatibah/archive/2008/08/19/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-1b.aspx but the key SQL command is:

UPDATE foo SET geom = geom.STUnion(geom.STStartPoint());

Invalid geometries

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)