SQLite Forum

How to insert TEXT data from SQL server to SQLite with SQLite ODBC Driver
Login

How to insert TEXT data from SQL server to SQLite with SQLite ODBC Driver

(1) By anonymous on 2021-10-30 05:59:41 [link]

Greetings, I didn't manage to find same problem here on any other platform, so'll try luck and ask here :). I'm struggling with inserting text data FROM SQL server TO SQLite. The SQLite is as linked server. I'm using SQLite ODBC Driver from Mr. Werner. 
When I try to insert float/int there is no problem, but when it comes to inserting str/datetime to target column thats type is text I ran into error "Das abfragebasierte Einfügen oder Aktualisieren von BLOB-Werten wird nicht unterstützt".

From what I've understood for some reason the query has the feeling that sqlite target columns data type is BLOB, which is not. 

I've also tried cast & convert data type inside query on various sql server text types.

Only thing that worked but not perfectly, is create blob col in sqlite table & transform inside query to nvarbinary(max) and then insert text data. 

Any help is appreciated.

SYSTEM:
SQL SERVER 2017 - 64bit
SQLite 3.32.3
SQLITE ODBC driver for Win64
Windows 10 Pro

(2) By Gunter Hick (gunter_hick) on 2021-10-30 09:41:18 in reply to 1

This is most probably an issue with the ODBC driver. SQLite does not return German language error messages. The error message would indicate that INSERT ... INTO is not supported by the driver.

You have also failed to provide the schema of the table you are attempting to insert into, so maybe your assertion of target column type is mistaken.

Whart do you mean with "SQLite is as linked server"? SQLite is a library that is either statically or dynamically linked into your application. There is no "server" like in client/server database systems.