Exporting from SQLite to SQL Server

Question

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

Answer 1

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.

Answer 2

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
    }

Answer 3

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

Answer 4

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.

Answer 5

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.

Answer 6

For Android.

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