有没有工具可以将 SQLite 数据库迁移到 SQL Server(包括结构和数据)?
SQLite提供了一个.dump选项,可在命令行中运行。虽然我更喜欢使用SQLite数据库浏览器应用程序来管理SQLite数据库。您可以将结构和内容导出为可被几乎任何工具读取的.sql文件。选择 文件 > 导出 > 数据库为 SQL 文件。
我知道这是一个旧的帖子,但我认为这个解决方案也应该在这里。
然后在SQL Server下以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
然后,您可以以普通用户的身份运行查询,例如:
SELECT * INTO SQLServerDATA FROM openquery(SQLiteDNSName, 'select * from SQLiteData')
或者您可以像这个那样,用于处理较大的表格。
SQLite的.dump
命令将整个数据库的内容输出为ASCII文本文件。该文件采用标准SQL格式,因此可以导入到任何SQL数据库中。
更多详细信息请参见此页面:sqlite3
sqlite-manager,是Firefox的一个插件,它允许你将SQLite数据库导出为SQL脚本。
数据库>导出数据库>导出到文件
(修正Firefox 35个错误需要根据以下网页所示更正扩展代码:如何修复可选的SQLite管理器模块以使其工作)
命令行:
sqlite3 DB_name .dump > DB_name.sql
将sqlite数据库导出为SQL脚本。
针对Android。
adb root
adb shell
cd /data/com.xxx.package/databases/
sqlite3 db_name .dump >dump.sql
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
}
sqlite3 my_db.db -batch ".dump" > my_db.sql
。 - Aaron R.