I am trying to import some shape files into SQL Server 2008 R2. I found this useful tool called Shape2SQL however I am not sure how I can import shape file directly using only SQL scripts as this is important for build automation and deployment. So far I haven't found anything useful just yet. Would anyone be able to point some directions? Thanks.
I would equate this to asking for a SQL script that imported an excel file to SQL Server. Even if it were possible, you would be better off if you did not use only SQL scripts to automate the deployment of information.
Its like asking for a screwdriver to hammer in a nail. SQL Server doesn't understand things like shapefile format and excel spreadsheets because that's not what a DBMS is for: it's what DTS is for.
To answer your question, however, GDAL has been the DTS of spatial information for quite some time now. Have a look at the following command to import spatial information to SQL Server using ogr2ogr:
After installing GDAL, just modify the connection string and data source in the above command to suit your needs. Note that ogr2ogr also supports multiple other formats.