Currently I do an extract with SQLCMD from an old SQL Server DB where the varchar column is latin1 and I generate INSERT statements into a file to load the data into a MariaDB database:
INSERT INTO maria_table (id, name, description) VALUES
(210,'ALL.M.USDBBL','Refining GOA All Monthly')
,(211,'S.M.USDBBL','RefinGdd 2 BBL'
,(212,'R.M.USDBBL','RefinGdd 2BL')
, etc
using the SQL Server command and options:
SQLCMD.EXE -i "generate-inserts.sql" -h-1 -S "my-host" -d Tardis -r1 -W -b -f o:65001 > inserts.sql
Also tried the -u flag instead with no luck.
This is the SQL:
SELECT CONCAT('INSERT INTO maria_table (id, name, description)',
' VALUES ');
SELECT CONCAT(IIF(row_number() OVER (ORDER BY id ) = 1, ' ', ','),
'(',
id, ',',
'''', dbo.encode4MySql(name), ''',',
'''', dbo.encode4MySql(description), ''')'
)
FROM sql_srv_table WITH (NOLOCK)
ORDER BY id;
SELECT ';';
SELECT 'COMMIT;';
and this is the function I need to develop:
CREATE FUNCTION dbo.encode4MySql(@raw VARCHAR(4000))
RETURNS VARCHAR(4000) AS BEGIN
DECLARE @replaced VARCHAR(4000);
SET @replaced = REPLACE(@raw, '''', '''''');
??? what do I do here to replace those special characters?
RETURN @replaced;
END;
All I am replacing so far is the '.
But I get errors like this from MariaDB:
ERROR 1366 (22007) at line 384:
Incorrect string value: '\x87ao Su...' for column 'description' at row 1995
I asked a similar question here SQL Server dump of varchar data to file loads in mysql but not mariadb but essentially didn't get the whole solution, so I'm asking more focused question this time about how to encode that and further incompatible characters.
As someone on the previous question commented, "\x87 is potentially the character ‡ in Latin1".
I'm also unable to locate the special character in the dump file - searching with any combination of \x87ao or ‡ doesn't work.
