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 and/or SSIS 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:
ogr2ogr -overwrite -f MSSQLSpatial "MSSQL:server=.\MSSQLSERVER2008;database=spatial;trusted_connection=yes" "TG20.shp"
GDAL Binaries (Assuming Windows) are available from this list (via). You can install according to this process, but you should ignore the steps about python.
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.
I have same problem, my understanding is that the stored data is correct, if you consider it a binary utf-8 encoded data, to convert this data to nvarchar i wrote this (partially tested) UDF function in sql server:
CREATE FUNCTION varbinUTF8_to_nvarchar ( @data VARBINARY(MAX) )
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @n INT
DECLARE @buffer NVARCHAR(MAX)
SET @buffer = N''
SET @n = DATALENGTH(@data)
DECLARE @i INT
SET @i = 0
DECLARE @byte INT
DECLARE @byte2 INT
DECLARE @byte3 INT
DECLARE @byte4 INT
DECLARE @decoded NCHAR
WHILE @i < @n
BEGIN
SET @i = @i + 1
SET @byte = SUBSTRING(@data, @i, 1)
IF ( @byte < 0x7f )
SET @decoded = NCHAR(@byte)
ELSE
IF @byte & 0xe0 = 0xc0
BEGIN
SET @i = @i + 1
SET @byte2 = SUBSTRING(@data, @i, 1)
SET @decoded = NCHAR(( ( @byte & 0x1F ) * 0x40 )
+ ( @byte2 & 0x3f ))
END
ELSE
IF @byte & 0xf0 = 0xe0
BEGIN
SET @i = @i + 1
SET @byte2 = SUBSTRING(@data, @i, 1)
SET @i = @i + 1
SET @byte3 = SUBSTRING(@data, @i, 1)
SET @decoded = NCHAR(( ( @byte & 0xF )
* 0x1000 ) + ( ( @byte2
& 0x3f ) * 0x40 )
+ ( (@byte3 & 0x3f) ))
END
ELSE
IF @byte & 0xf8 = 0xf0
BEGIN
SET @i = @i + 1
SET @byte2 = SUBSTRING(@data, @i, 1)
SET @i = @i + 1
SET @byte3 = SUBSTRING(@data, @i, 1)
SET @i = @i + 1
SET @byte4 = SUBSTRING(@data, @i, 1)
/*
EDIT 2014-11-27 THIS PART WERE WRONG
SET @decoded = NCHAR(( ( @byte & 0xF )
* 0x1000 )
+ ( ( @byte2 & 0x3f )
* 0x40 )
+ ( ( @byte3 & 0x3f )
* 0x40 )
+ ( (@byte4 & 0x3f) ))
FOLLOWS PATCH, but sql server does not support unicode chars > 16 bit
*/
SET @decoded = NCHAR(( ( @byte & 0x7 )
* 0x40000)
+ ( ( @byte2 & 0x3f )
* 0x1000 )
+ ( ( @byte3 & 0x3f )
* 0x40 )
+ ( (@byte4 & 0x3f) ))
END
ELSE
BEGIN
return N'BAD UTF-8 MESSAGE'
END
SET @buffer = @buffer + @decoded
END
return @buffer
END
Then i can convert the wrong formatted field using something like:
SELECT
dbo.varbinUTF8_to_nvarchar(CONVERT(VARBINARY(MAX), name_1) )
FROM
dbo.che_adm1
In your specific case you should be able to get nvarchar data doing :
SELECT languagecode, dbo.varbinUTF8_to_nvarchar(CONVERT(VARBINARY(MAX), name) as name, longitude, latitude
FROM tempdb.dbo.london_points;
Best Regards
Andrea
Best Answer
Ok so it seems I've found the solution. The problem is that the current version of QGIS (GDAL version is the real problem) has a bug and it doesn't create the geography\geometry column in MSSQL...
Installing QGIS 3.4.11 that has GDAL 2.4.1 and using the following command worked:
part of the solution was found here (unfortunately this result was far down in the google search): https://github.com/OSGeo/gdal/issues/3140