Exporting from SQLite to SQL Server

Question

Is there a tool to migrate an SQLite database to SQL Server (both the structure and data)?

score 47 · Accepted Answer · edited May 07 '13 at 20:57

47

SQLite does have a .dump option to run at the command line. Though I prefer to use the SQLite Database Browser application for managing SQLite databases. You can export the structure and contents to a .sql file that can be read by just about anything. File > Export > Database to SQL file.

edited May 07 '13 at 20:57

Peter Mortensen

30,738
21
105
131

answered Oct 02 '08 at 15:48

swilliams

48,060
27
100
130

3

The exported file does need some tweaking for diferences in syntax but nothing too complex. Thanks – Geoff Appleford Oct 02 '08 at 16:09
1

Technically, the SQLite shell is not SQLite but a console application linked to SQLite which only is a library. – Benoit Jan 25 '11 at 17:04
4

This pointed me in the right direction, but I was looking for the syntax, so I'll include it here: `sqlite3 my_db.db -batch ".dump" > my_db.sql` – Aaron R. Apr 02 '17 at 16:59
in my case it is complex.... a field contains date and time and when exported, it exported using milliseconds, which was incompatible when I tried to execute it in SQL Server :-( – jstuardo Dec 13 '18 at 15:41
There are A LOT of errors on the sql file that it created, do I submit a new question for this or what am I doing wrong? – Shayan Nov 20 '21 at 13:54
Some post processing tweaks: -replace `CREATE TABLE IF NOT EXISTS` with `CREATE TABLE` -you maybe need the order of some create table statements because of foreign key references. Be sure to create table before a fk can reference it! -uncheck `multiple rows per insert statement` since there is a 1000 row limit – Jan Jul 02 '23 at 12:27
Also if you want it to run with `sqlcmd` then be sure to replace `"..."` with `[...]` where `...` is a field/column, datatype or tablename. This extra step is not needed when you run it inside MSSQL Management Studio. – Jan Jul 02 '23 at 13:19

score 13 · Answer 2 · edited May 23 '17 at 10:30

I know that this is old thread, but I think that this solution should be also here.

Install ODBC driver for SQLite
Run odbcad32 for x64 or C:\Windows\SysWOW64\odbcad32.exe for x86
Create SYSTEM DSN, where you select SQLite3 ODBC Driver
Then you fill up form where Database Name is filepath to sqlite database

Then in SQL Server run under sysadmin

USE [master]
GO
EXEC sp_addlinkedserver 
   @server     = 'OldSQLite', -- connection name
   @srvproduct = '',          -- Can be blank but not NULL
   @provider   = 'MSDASQL', 
   @datasrc    = 'SQLiteDNSName' -- name of the system DSN connection 
GO

Then you can run your queries as normal user e.g.

SELECT * INTO SQLServerDATA FROM openquery(SQLiteDNSName, 'select * from SQLiteData')

or you can use something like this for larger tables.

SELECT * INTO SQLServerDATA FROM openquery(OldSQLite, 'select * from SQLiteData') ------------------- And you can install driver for 64 bits and create a ODBC for 64 bits (odbcad32 in system32 folder - SQLite3 ODBC Driver) — R.Alonso, May 26 '17 at 22:26

score 9 · Answer 3 · edited May 07 '13 at 20:58

9

The SQLite .dump command will output the entire contents of the database as an ASCII text file. This file is in standard SQL format, so it can be imported into any SQL database. More details on this page: sqlite3

edited May 07 '13 at 20:58

Peter Mortensen

30,738
21
105
131

answered Oct 02 '08 at 15:51

Nathan Clark

121
2

1

well the .dump command by itself outputs to the screen. you also need to use the .output command to get it into a file. – aris Jan 11 '19 at 00:21

score 8 · Answer 4 · edited May 23 '17 at 12:10

8

sqlite-manager, firefox add-on: allows you to export an SQLite database in a SQL script.

Data Base>Export Database>Export to file

(Correction firefox 35 bugg obliged to correct the extension code as indicate to the following web page: How to fix your optional sqlite manager module to work)

Command line :

sqlite3 DB_name .dump > DB_name.sql

exports the sqlite database in a SQL script.

From url : http://doc.ubuntu-fr.org/sqlite.

edited May 23 '17 at 12:10

Community

1
1

answered Jan 26 '15 at 13:53

AlbanMar31

637
1
6
12

the add on appears to be dead now, firefox version 57.0 shows it as incompatible and unusable... – me_ Nov 23 '17 at 21:00

score 0 · Answer 5 · answered Nov 07 '16 at 11:10

A idea is do some thing like this: - View squema in sql lite and get the CREATE TABLE command. - Execute, parsing sql, in SQL SERVER - Travel data creating a INSERT statment for each row. (parsing sql too)

This code is beta, because no detect type data, and no use @parameter and command object, but run.

(You need insert reference and install System.Data.SQLite;)

c#: Insert this code (or neccesari) in head cs

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using System.Data.SQLite;

using System.Threading;

using System.Text.RegularExpressions;

using System.IO;

using log4net;

using System.Net;

    public static Boolean SqLite2SqlServer(string sqlitePath, string connStringSqlServer)
    {
        String SqlInsert;
        int i;
        try
        {

            string sql = "select * from sqlite_master where type = 'table' and name like 'YouTable in SQL'";
            string password = null;
            string sql2run;
            string tabla;
            string sqliteConnString = CreateSQLiteConnectionString(sqlitePath, password);
            //sqliteConnString = "data source=C:\\pro\\testconverter\\Origen\\FACTUNETWEB.DB;page size=4096;useutf16encoding=True";

            using (SQLiteConnection sqconn = new SQLiteConnection(sqliteConnString))
            {



                sqconn.Open();

                SQLiteCommand command = new SQLiteCommand(sql, sqconn);
                SQLiteDataReader reader = command.ExecuteReader();

                SqlConnection conn = new SqlConnection(connStringSqlServer);
                conn.Open();
                while (reader.Read())
                {
                    //Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);
                    sql2run = "" + reader["sql"];
                    tabla = "" + reader["name"];

                    /*
                    sql2run = "Drop table " + tabla;
                    SqlCommand cmd = new SqlCommand(sql2run, conn);                       
                    cmd.ExecuteNonQuery();
                    */



                    sql2run = sql2run.Replace("COLLATE NOCASE", "");
                    sql2run = sql2run.Replace(" NUM", " TEXT");
                    SqlCommand cmd2 = new SqlCommand(sql2run, conn);
                    cmd2.ExecuteNonQuery();


                    // insertar los datos.
                    string sqlCmd = "Select *  From " + tabla;
                    SQLiteCommand cmd = new SQLiteCommand(sqlCmd, sqconn);
                    SQLiteDataReader rs = cmd.ExecuteReader();
                    String valor = "";
                    String Valores = "";
                    String Campos = "";
                    String Campo = "";
                    while (rs.Read())
                    {
                        SqlInsert = "INSERT INTO " + tabla;
                        Campos = "";
                        Valores = "";
                        for ( i = 0; i < rs.FieldCount ; i++)
                        {

                            //valor = "" + rs.GetString(i);
                            //valor = "" + rs.GetName(i);
                            Campo = "" + rs.GetName(i);
                            valor = "" + rs.GetValue(i);

                            if (Valores != "")
                            {
                                Valores = Valores + ',';
                                Campos = Campos + ',';
                            }
                            Valores = Valores + "'" + valor + "'";
                            Campos = Campos + Campo;
                        }
                        SqlInsert = SqlInsert + "(" + Campos + ") Values (" + Valores + ")";
                        SqlCommand cmdInsert = new SqlCommand(SqlInsert, conn);
                        cmdInsert.ExecuteNonQuery();


                    }


                }

                }
            return true;
        } //END TRY
        catch (Exception ex)
        {
            _log.Error("unexpected exception", ex);

            throw;

        } // catch
    }

score 0 · Answer 6 · answered Jun 19 '19 at 07:53

0

For Android.

adb root
adb shell
cd /data/com.xxx.package/databases/
sqlite3 db_name .dump >dump.sql

answered Jun 19 '19 at 07:53

caopeng

914
13
23

Exporting from SQLite to SQL Server

6 Answers6

Linked