Sqlite: copy data from all db1 tables to db2 tables

Question

0.00/5 (No votes)

See more:

I have came across a data migration requirement. If there is change in DB structure, I have to first take copy of DB then delete this existing DB, create a new DB with same name and dump all the data from copied db to new DB. new DB may have some new tables or new columns in existing tables.

To implement above mentioned task, I have planned to create Console application or Windows Service using C# language.

What I have tried:

I am able to copy db to temp location using

C#

BackupDatabase

method of

C#

System.Data.SQLite

Regarding data restore to new DB...

Used below SQLite command in SQLite browser to copy data of single table

SQL

END TRANSACTION;
ATTACH 'C:\Program Files\DB Browser for SQLite\TestDB.db' AS SourceDB;
ATTACH 'D:\Documents\Demo Applications\Windows\WindowsFormsApplication_SQLite\Database\TestDB.db' AS DestDB;
BEGIN TRANSACTION;
INSERT INTO DestDB.TestEmployee SELECT * FROM SourceDB.TestEmployee;
COMMIT;

BUT, I'am not sure how to perform above operation on all the all the table exists in source DB. I.e. loop through all the tables to copy and insert data.

Another challenge is of change in table structure. I.e. If table a1 having two columns in back up DB (from which I have to copy data) and a1 table of destination DB having 3 columns (i.e. one new column) then query mentioned in an example fails giving error: table DestDB.TestEmployee has 3 columns but 2 values were supplied

How could this column mismatch can be handled, or is there another elegant way to do data migration from one db to another.

Posted 21-Nov-16 20:32pm

sopy7

Updated 7-Dec-16 0:40am

Add a Solution

2 solutions

Solution 1

Below is sample of copying data from one database to another database

C#

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, LiteAccess;

var
  LiteConnection: TLiteConnection;
  LiteQuery: TLiteQuery;
begin
  LiteConnection := TLiteConnection.Create(nil);
  try
    LiteConnection.Options.ForceCreateDatabase := True;
    LiteConnection.Database := 'd:\src.db3';
    LiteConnection.Connect;
    LiteConnection.ExecSQL('create table t_test (f_id integer)');
    LiteConnection.ExecSQL('insert into t_test values(1),(2),(3),(4),(5)');
    LiteConnection.Disconnect;
    LiteConnection.Database := 'd:\dst.db3';
    LiteConnection.Connect;
    LiteConnection.ExecSQL('create table t_test (f_id integer)');
    LiteConnection.ExecSQL('attach "d:\src.db3" as source');
    LiteConnection.ExecSQL('insert into main.t_test select * from source.t_test');
    LiteQuery := TLiteQuery.Create(nil);
    try
      LiteQuery.Connection := LiteConnection;
      LiteQuery.SQL.Text := 'select * from main.t_test';
      LiteQuery.Open;
      while not LiteQuery.Eof do begin
        WriteLn(LiteQuery.FieldByName('f_id').AsString);
        LiteQuery.Next;
      end;
    finally
      LiteQuery.Free;
    end;
  finally
    LiteConnection.Free;
    readln;
  end;
end.

Check this also these helpful link

Copy data from one table to another table in SQL Server 2008 R2[^]

sql - sqlite copy data from one table to another - Stack Overflow[^]

Thanks

Posted 21-Nov-16 22:38pm

Allen Scott

Comments

sopy7 30-Nov-16 1:05am

Statement insert into main.t_test select * from source.t_test, assumes that both source and destination tables have same columns, but in my situation, destination table may have additional columns (i.e. Columns newly added). In that case this statement ("insert into main.t_test select * from source.t_test" won't work).

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sopy7 · Accepted Answer · 2016-12-07T00:40:00

I have found the solution..

To tackle the problem of difference in table structure, I am maintaining a separate table named dbShema which contains two columns "TableName" and "Columns"; In this table I maintain all the table names and all the column names separated by comma in "Columns" column.

I fetch all tables and its corresponding columns in SQLReader and loop through it to create a dynamic SQLite insert query. Then I execute this query to copy data from source to destiantion DB.

Code:

C#

StringBuilder strSql = new StringBuilder();
            strSql.Append("ATTACH 'D:\\Application\\Demo Applications\\Windows\\WindowsFormsApplication_SQLite\\Database\\Source\\TestDB.db' AS SourceDB;");
            strSql.Append("ATTACH 'D:\\Application\\Demo Applications\\Windows\\WindowsFormsApplication_SQLite\\Database\\Destination\\TestDB.db' AS DestDB;");
            
            using (SQLiteConnection con = new SQLiteConnection(SourceconnectionString))
            {
                con.Open();

                using (SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM dbSchema", con))
                {
                    using (SQLiteDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read()) // Reading Rows
                        {
                            strSql.Append("INSERT INTO DestDB.");
                            strSql.Append(rdr["TableName"].ToString());
                            strSql.Append(" (");
                            strSql.Append(rdr["Columns"].ToString());
                            strSql.Append(") ");
                            strSql.Append("SELECT ");
                            strSql.Append(rdr["Columns"].ToString());
                            strSql.Append(" FROM SourceDB.");
                            strSql.Append(rdr["TableName"].ToString());
                            strSql.Append(";");
                            var tableName = rdr["TableName"].ToString();
                            var Columns = rdr["Columns"].ToString();
                        }
                    }
                }
            }

            using (SQLiteConnection conn = new SQLiteConnection(DestinationConnectionString))
            {
                conn.Open();
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {
                    cmd.CommandText = strSql.ToString();
                    int i = cmd.ExecuteNonQuery();
                    if (i > 0)
                    {
                        MessageBox.Show("Data dumped successfully ...!!!");
                    }
                }
            }

But, I am using SQLReader and stringbuilder for looping and concatenating.

Is there more efficient way to achieve this. ????