select b.[Region],geometry::UnionAggregate(geom.MakeValid()) AS Geo
 into dbo.DRShape from [dbo].[departements2] a
 join [dbo].[BM_REGIONFR] b on a.[code_insee] = b.[dep_2] group by b.Region

ogr2ogr -f "ESRI Shapefile" "C:Userssqlexport.shp" "MSSQL:server=PORT_7FMW8H2;database=testdbspatial;trusted_connection=yes;" -sql "select * from DRShape" -overwrite