I am trying to load a SQL Server database table from a SQLite database. I have been unable to transfer the data between the two databases since there are:
- Difficulties in accessing the data in the SQLite database
- Difficulties in accessing the individual columns within that database.
How do I perform this image data transfer, using T-SQL?
Attempts so far:
The source database is a linked server (SQLiteIDImagerThumbs) in SQL Server. This was created from the SQLite database. This linked server has one table, idThumbs.
The target database is a SQL Server database (idimagerthumbs). This DB has only one table, idThumbs defined with similar columns to SQLIDImagerThumbs.
So far I have:
Scripted a SELECT statement from [SQLiteIDImagerThumbs]...[idThumbs].
SELECT * FROM [SQLiteIDImagerThumbs]...[idThumbs]
Running this SELECT statement results in the following error:
Msg 7347, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' for linked server 'SQLiteIDImagerThumbs' returned data that does not match expected data length for column '[SQLiteIDImagerThumbs]...[idThumbs].idThumbs.idThumb'. The (maximum) expected data length is 255, while the returned data length is 10627.
Manually creating a SELECT statement on SQLiteIDImagerThumbs.
SELECT GUID, ImageGUID, ...other columns, idThumb FROM [SQLiteIDImagerThumbs]...[idThumbs];
Running this SELECT statement results in the following errors:
Msg 207, Level 16, State 1, Line 1 Invalid column name 'GUID'. Msg 207, Level 16, State 1, Line 2 Invalid column name 'ImageGUID'. ... other column errors Msg 207, Level 16, State 1, Line 8 Invalid column name 'idThumb'.
I have also conducted many web searches to try and find the answer to this. These have produced so much information that I am completely lost. Most of the information I have found concerns inserting a single file into an image colum; using BULK_INSERT, READTEXT, OPENROWSET(BULK) etc. I have not managed to find any information that exactly matches my problem.
I am trying to do a substantial data recovery and conversion of data produced by an application which is no longer supported, and for which I do not have any source code. The application is a very small shell on what is substantially a database application. The permissions and users on the SQLite database have been set by the application. The database I am trying to convert is about 8GB in size. Accessing the data is a trivial step in a much larger data recovery and conversion exercise, but is proving intractable.
The schema (created from the SQLite DB using DB Browser) of the SQLite database is:
CREATE TABLE idThumbs ( GUID varchar(50) PRIMARY KEY, ImageGUID varchar(50), ... other columns idThumb BLOB)
The schema of the SQL Server DB (created from a script available in the application) is:
CREATE TABLE [dbo].[idThumbs]( [GUID] [nvarchar](50) NOT NULL, [ImageGUID] [nvarchar](255) NULL, ... other columns [idThumb] [image] NULL, PRIMARY KEY CLUSTERED
Issues arising from comments/answers
Using SQL Server to script Update/Insert/Delete Statements creates error:
-- [SQLiteIDImagerThumbs]...[idThumbs] contains no columns that can be inserted or the current user does not have permissions on that object.
The following queries:
SELECT <column_name> FROM IDTHumbs SELECT <column_name>, LENGTH(<column_name>) WHERE LENGTH(<column_name>) > <column_name_declared_length>
work correctly for all columns when called from DB Browser. There is no data longer than the declared_length. When called from SSMS via the linked server I get the errors from action 2 above.
Lazy schema validation does not appear on the properties of the linked server. I do not understand how installing developer on my PC (which I have) gives a capability that you say is only available under the Enterprise version.
All files are on my PC. I have the latest version of SQLite, DB Browser, and the SQLite ODBC driver. For a variety of reasons I am stuck with SQL Server 2005 Developer's edition. This is the database environment which the application used.
As part of the larger project, the first thing I did was to set up a development environment, with the tools necessary to re-initialise the databases. This removes the possibility of trashing the original files.
I would be quite happy to use something like excel. I can obtain the data in a CSV file from DB Browser. I do not know how to handle image data in a CSV file, as whatever delimiter is chosen, it is possible that it will appear as part of the data; and there is no way of escaping delimiters within the data.