If you are a developer, you can develop an application to perform synchronization. Would you do
SELECT name FROM sqlite_master WHERE type='table'
to get table names, you can recreate them in Oracle (you can make DROP TABLE tablename in Oracle first, to avoid conflict, assuming SQLite will be authoritative) using the CREATE TABLE commands. Getting the columns for each one takes
SELECT sql FROM sqlite_master WHERE type='table' and name='MyTable'
And then you should analyze the result:
string columnNames = sql.replace(/^[^\(]+\(([^\)]+)\)/g, '$1').replace(/ [^,]+/g, '').split(','); string[] columnArray = columnNames.Split(','); foreach (string s in columnArray) {
A StringBuilder can be used to collect the table name with its columns to create an INSERT . To add values, you just need to make SELECT * FROM MyTable for each of the tables during the loop through the names of the tables that you received from the original query. You will StringBuilder over the columns of the rows of the returned data and add the values to the StringBuilder :
INSERT INTO MyTable ( + columnA, columnB, etc. + ) VALUES ( datarow [0], datarow [1], etc. + ) .
Not really, but - you fill in the data by adding the column name and its data as you go through the loops. You can get the column names by adding s to this foreach above. Each column value is then set using the foreach , which gives you each object obj in drData.ItemArray . If all you have is string fields, just simply add obj.ToString() to your StringBuilder for each column value in your query, as shown below. Then you run the query after collecting all the column values for each row. You use a new StringBuilder for each row - it needs to get reset to INSERT INTO MyTable ( + columnA, columnB, etc. + ) VALUES ( before each new row, so new column values can be added.
If you have mixed data types (e.g. DATE , BLOB , etc.), you will need to determine the types of columns in this path, save them in a list or array, and then use the counter to determine the index of this list / array slot and get type, so you know how to translate your object to what Oracle can use - does that just add to_date() to the result with formatting for the date (since SQLite stores them as date strings with the format yyyy-MM-dd HH:mm:ss ) or add it to OracleParameter for a BLOB and send it along with the RunOracleCommand function. (I did not go into this, below.)
Putting all this together, we get the following:
string[] columnArray = null; DataTable dtTableNames = GetSQLiteTable("SELECT name FROM sqlite_master WHERE type='table'"); if (dtTableNames != null && dtTableNames.Rows != null) { if (dtTableNames.Rows.Count > 0) { // We have tables foreach (DataRow dr in dtTableNames.Rows) { // Do everything about this table here StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO " + tableName + " ("); // we will collect column names here string tableName = dr["NAME"] != null ? dr["NAME"].ToString() : String.Empty; if (!String.IsNullOrEmpty(tableName)) { RunOracleCommand("DROP TABLE " + tableName); RunOracleCommand("CREATE TABLE " + tableName); } DataTable dtColumnNames = GetSQLiteTable("SELECT sql FROM sqlite_master WHERE type='table' AND name='"+tableName+"'"); if (dtColumnNames != null && dtColumnNames.Rows != null) { if (dtColumnNames.Rows.Count > 0) { // We have columns foreach (DataRow drCol in dtTableNames.Rows) { string sql = drCol["SQL"] != null ? drCol["SQL"].ToString() : String.Empty; if (!String.IsNullOrEmpty(sql)) { string columnNames = sql.replace(/^[^\(]+\(([^\)]+)\)/g, '$1').replace(/ [^,]+/g, '').split(','); columnArray = columnNames.Split(','); foreach (string s in columnArray) { // Add column to table using: RunOracleCommand("ALTER TABLE " + tableName + " ADD COLUMN " + s + " NVARCHAR(250)"); // can hard-code like this or use logic to determine the datatype/column width sb.Append("'" + s + "',"); } sb.TrimEnd(","); sb.Append(") VALUES ("); } } } } // Get SQLite Table data for insertion to Oracle DataTable dtTableData = GetSQLiteTable("SELECT * FROM " + tableName); if (dtTableData != null && dtTableData.Rows != null) { if (dtTableData.Rows.Count > 0) { // We have data foreach (DataRow drData in dtTableData.Rows) { StringBuilder sbRow = sb; // resets to baseline for each row foreach (object obj in drData.ItemArray) { // This is simplistic and assumes you have string data for an NVARCHAR field sbRow.Append("'" + obj.ToString() + "',"); } sbRow.TrimEnd(","); sbRow.Append(")"); RunOracleCommand(sbRow.ToString()); } } } } } }
All of this assumes that you have a void RunOracleCommand() function that can take an SQL command and run it against Oracle DB and a GetSQLiteTable() function that can return a DataTable from your SQLite database by passing it an SQL command.
Please note that this code is not verified, as I wrote it directly in this post, but it is largely based on the code I wrote for Oracle synchronization in SQLite, which has been tested and works.