Query Mapping between MS-SQL and SQLite

If for some reason you need to migrate your MS-SQL Server database to SQLite Database, among many other things, here are some things that you should pay attention to.

Things to consider:

  1. Each Statement must end with “;
  2. Remove references to schema on query
    1. eg: References to [dbo].
    2. Select * from [dbo].[table1] in SQL = select * from [table1] in SQLite
  3. Watch out for special functions
    1. Substring in SQL = Substr in SQLite
    2. Len in SQL = Length in SQLite
  4. Other things to watch out for:
    1. Setting Identity Column
      1. IDENTITY in SQL = AutoIncrement in SQLite
    2. Setting Primary Keys
      1. SQL has multiple ways to set Primary Keys, some ways do not work in SQLite
  5. Querying for Information Schema for tables/columns
    1. SQL has INFORMATION_SCHEMA.TABLES and SQLite has sqlite_master/sqlite_temp_master tables
  6. SQLite doesn’t support Truncate command
    1. Replace Truncate with Delete From
  7. Manually update DateTime value after inserting into SQLite to a string value
    1. DateTime in SQL and SQLite are different
    2. SQL: CAST(0x000090CE00000000 AS DateTime) shows valid date/time in SQL but shows different format in SQLite which is not readable by frameworks such as ADO.NET
    3. To be on safe side update with string value.
    4. Eg: update table1 set create_dt= ‘2018-03-30 00:36:10.520’;