Skip to content
IntoSQL home
Convert a file

Migration guides

SQL Server to SQLite migration guide

Convert a SQL Server script from SSMS into a SQLite script with the same tables, keys and data: how to script, convert, load, and every rule applied.

View as Markdown

IntoSQL reads the script SSMS writes with Generate Scripts and writes one SQLite script with the same tables, columns, keys, indexes, foreign keys, defaults, counters, comments and rows. It reads the script itself, without a database server, and lists in the report every value SQLite can't hold as it is and every object it doesn't carry. The script loads on SQLite 3.31 and later.

Convert in the browser: SQL Server to SQLite opens the converter set up for this pair.

Script for the best result

In SQL Server Management Studio, right-click the database, choose Tasks, Generate Scripts, pick the tables, and under Advanced set Types of data to script to Schema and data. Save it as Unicode (SSMS's default) or UTF-8, as .sql or .sql.gz. dbatools (Export-DbaScript), mssql-scripter and other tools built on SQL Server Management Objects write the same script, from SQL Server 2017 on.

  • A script of the data alone still converts: the types are read from the values, and the report says the schema was missing (no_schema).
  • Tables on a partition scheme arrive as one table with every partition's rows (partitions_dropped), and a temporal table and its history table arrive as two plain tables.
  • SSMS doesn't script the values of computed and rowversion columns; computed columns are written again, and rowversion columns are reported (rowversion_empty).

Convert

In the browser, open SQL Server to SQLite or the converter on the home page, drop the script and choose SQLite. It reads the file from your disk in pieces, twice, and writes the new script as it goes, so the file is never uploaded and its size isn't limited by memory. The desktop app does the same with the file on disk.

With the command line:

Terminal
intosql inspect shop.sql
intosql convert shop.sql --to sqlite --output shop.sqlite.sql --report shop.report.json

inspect lists the tables, their rows and what won't be converted, without writing anything. convert writes the script and, with --report, the report as JSON.

Through the API, a script converts like any other file: open a conversion with its name ending in .sql or .sql.gz and "output": {"dialect": "sqlite"}.

Load into SQLite

Terminal
sqlite3 -bail shop.sqlite < shop.sqlite.sql

The script creates each table with its keys, copies the rows and adds indexes and counters, all in one transaction with syncing off, so it runs at disk speed and a load that stops leaves no partial tables. Schemas other than dbo become a prefix on their tables' names (app.orders is app_orders). Dates and times are ISO 8601 text with every digit SQL Server kept, and datetimeoffset values are written in UTC (moments_as_utc). Foreign keys are declared in each table and checked once the rows are in: the script ends with PRAGMA foreign_key_check, which lists any row that points to a missing row instead of failing the load.

Or load it straight in

intosql load and the desktop app load the dump into SQLite without a script, with the same tables, keys and indexes the script creates:

Terminal
intosql load shop.sql --into shop.db

It stops before writing anything when one of the dump's tables already exists. It loads in one transaction, and a file the load created is removed if it fails. Loading into SQLite is free. See Loading.

What isn't converted

Views, functions, procedures, triggers, synonyms, rules and security policies are written in T-SQL. They are listed in the report by kind and name, never dropped silently, so you can recreate them in SQLite.

Data types

SQL Server to SQLite data types lists each type that changes in the script: what it becomes, why, and the report line.

Every other rule

Generated from the engine, where each rule is decided.

In SQL Server In SQLite Why Report line
collations that ignore case (CI) exact comparison SQLite compares text exactly unless told otherwise, and its NOCASE only folds ASCII letters. case_insensitive_collation
CREATE SEQUENCE not created SQLite has no sequences. The values a sequence handed out are in the rows. sequence_not_converted
computed columns GENERATED ALWAYS AS, STORED when PERSISTED, VIRTUAL otherwise SQLite 3.31 and later compute them the same two ways, with CONCAT written so NULL counts as empty as in SQL Server. None
computed columns with functions SQLite lacks a plain column A formula that can't be written leaves a plain column and a report line; SSMS scripts no values for computed columns. generated_not_translated
defaults with functions SQLite lacks, and NEXT VALUE FOR no default A default that can't be written is left out and reported. default_not_translated
CHECK constraints translated The same checks, tested as each row loads. None
CHECK constraints with functions SQLite lacks not created A check that can't be written is left out and reported. check_skipped
CHECK constraints added WITH NOCHECK or disabled not created SQL Server doesn't apply these checks, so the rows may break them. check_not_enforced
filtered and columnstore indexes not created IntoSQL writes b-tree indexes on whole columns; create the others again after loading. index_skipped
foreign keys in CREATE TABLE, loaded unchecked, then listed by PRAGMA foreign_key_check SQLite takes foreign keys only in CREATE TABLE and checks them only with PRAGMA foreign_keys on. The rows load unchecked and the script ends listing any row without a parent. None
foreign keys to tables the dump doesn't create not created A key can't name a table that isn't there. foreign_key_skipped
tables on a partition scheme one table with every partition's rows The rows are the same; partition the table again in SQLite if it needs it. partitions_dropped
temporal tables (SYSTEM_VERSIONING) two tables: the current rows and the history The table and its history table are copied as plain tables; SQLite doesn't keep the history by itself. system_versioning_dropped
schemas one file, tables named after their schema first A SQLite file is one database, so the schema name keeps tables with the same name apart; tables in dbo keep their names. schemas_merged
NOT NULL columns with values that become NULL NOT NULL dropped A column whose values had to become NULL can't keep NOT NULL, or the load would stop. not_null_relaxed
views, functions, procedures, triggers, synonyms, types and partition functions listed in the report Their code is written in T-SQL, so they are listed for you to recreate. not_converted_objects
scripts without CREATE TABLE (data only) types read from the values A data-only script has only rows, so the types come from the values. no_schema
expressions in INSERT values NULL A converter that doesn't run a database can't evaluate them. expression_value
text decoded twice (ñ) copied as it is The script holds exactly these characters, so they are copied; the report points them out. mojibake_suspected