Exporting data to MS SQL Server

The fastest way to load data into MS SQL Server is to make use of the MS SQL’s Bulk Copy Program (BCP). Fortunately OGR can make use of BCP. Add the following additional parameter to a spatial data transformation task.

--config MSSQLSPATIAL_USE_BCP TRUE

Ensure that the OGR version is 3.10.0 or above. Earlier versions may not be enabled for BCP. If a non-compatible OGR is used it is likely that the following error generated.

SQL Error SQLState=HY000, NativeError=0, Msg=[Microsoft][ODBC SQL Server Driver]Connection is not enabled for BCP

It will also need a compliant driver. “ODBC Driver 17 for SQL Server” and “ODBC Driver 18 for SQL Server” should work. The driver should be specified as part of the MS SQL Spatial connection in the OGR command.

ogr2ogr -f "MSSQLSpatial" "MSSQL:server=myserver,1433;database=mydatabase;driver={ODBC Driver 17 for SQL Server};uid=myuser;pwd=mypassword" "C:\iShareData\data\llpg.csv" -nln "destination_table" --config MSSQLSPATIAL_USE_BCP TRUE -lco OVERWRITE=YES -oo X_POSSIBLE_NAMES=easting -oo Y_POSSIBLE_NAMES=northing -lco GEOM_TYPE=GEOMETRY -a_srs "EPSG:27700"