LaPalme LaPalme - 2 months ago 18
SQL Question

Import tables from .sql to sqlite, incomplete SQL

I am trying to import tables from a database in an SQL Server to an SQLite database. Nevertheless, I think that the file tables.sql is not complete or correctly written.
From SQL Server Management Studio, I generated a script (tables.sql) selecting only some tables from the database. This file looks like this (with two tables) :

USE [MYDATABASE]
GO
SET IDENTITY_INSERT [dbo].[TABLE_ONE] ON

INSERT [dbo].[TABLE_ONE] ([TABLE_ONE_ID], [TABLE_ONE_MNEMO], [TABLE_ONE_DESC], [AUTO_ANALYSIS], [SCOPE_LEVEL], [IN_ABV], [TABLE_ONE_ORDER], [REPORT_ATA]) VALUES (37, N'GENERAL', N'GENERAL', N'N', 3, 0, 1, N'GEN')
...
GO
...
INSERT [dbo].[TEST_POINT_TYPE] ([TEST_POINT_TYPE_ID], [TEST_POINT_TYPE_MNEMO], [TEST_POINT_TYPE_DESC], [AUTO_ANALYSIS], [SCOPE_LEVEL], [IN_PATM], [TEST_POINT_TYPE_ORDER], [REPORT_ATA]) VALUES (207, N'LDMCR', N'LDMCR', N'N', 1, 1, 999, N'70')
SET IDENTITY_INSERT [dbo].[TABLE_ONE] OFF
SET IDENTITY_INSERT [dbo].[TABLE_TWO] ON

INSERT [dbo].[TABLE_TWO] ([TBT_ID], [TABLE_ONE_ID], [SUBPROGRAM_ID], [VALUE], [DESCRIPTION], [ETE], [SUBPROGRAM_CONTEXT], [IS_HOHO], [TEST_VISIBLE], [COMPLETION_ONLY], [CUPTER_NM_CHECKED_WORD], [CUPTER_NM_CHECKED_MAIN], [CUPTER_NM_CHECKED_MORE]) VALUES (1, NULL, 5, NULL, N'Air conditioning checks', N'21', NULL, NULL, NULL, NULL, NULL, NULL, NULL)
...
GO
...
INSERT [dbo].[TABLE_TWO] ([TBT_ID], [TABLE_ONE_ID], [SUBPROGRAM_ID], [VALUE], [DESCRIPTION], [ETE], [SUBPROGRAM_CONTEXT], [IS_HOHO], [TEST_VISIBLE], [COMPLETION_ONLY], [CUPTER_NM_CHECKED_WORD], [CUPTER_NM_CHECKED_MAIN], [CUPTER_NM_CHECKED_MORE]) VALUES (39, NULL, 5, NULL, N'Auto call-outs', N'31', NULL, NULL, NULL, NULL, NULL, NULL, NULL)
SET IDENTITY_INSERT [dbo].[TABLE_TWO] OFF


Now, if I try the following command :

cat tables.sql | sqlite3 MYDATABASE.db


I have the following statement

Error: incomplete SQL: ▒▒U


I suppose that something is missing in the tables.sql file and I tried to add this at the beginning :

CREATE DATABASE IF NOT EXISTS [MYDATABASE]


But it doesn't change anything. What may I change/add to complete the tables.sql file ? Then here is just an example with two tables, in reality I have much more tables and items.

Thank you for your help.

CL. CL.
Answer

This script uses lots of SQL Server-specific features that are not supported by SQLite.

SQLite has no CREATE DATABASE, USE, SET, or GO. Remove them.

SQLite has no schemans (not in the same way as SQL Server, only for attached databases). Remove all [dbo]. prefixes.

SQLite strings always are Unicode. Remove the N prefixes.

SQLite happens to supports SQL Server's [/] quoting.

There is no ▒▒U in the code you've shown, so there are likely to be other problems …