Using SQlite in Unity, what is the SqliteCommand to copy a table from one database to a different database, considering both tables are identical?

manpreet Best Answer 3 years ago

It correctly creates the Users table in the second (destination) .db database, however it does not populate it with the data of the Users table of the original .db database. The two tables are identical. None of them actually even have a primary key set. I need to copy the data from the original table to the destination table.

I have tried the following, without success for the copy part:

public class DatabaseAccess
{
    private static string _connDatabaseStringUsers = "URI=file:c:/TSV3/TS" + "DemoUsers" + ".db; Version=3; Journal Mode=Off; Synchronous=Off;";
}

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.Networking;
using UnityEngine.SceneManagement;
using System.Net;
using System.Net.Sockets;
using System.Text;
using System.Linq;
using Mono.Data.Sqlite;
using System.IO;
using System.Data;
using System;

public class GameInstance : Singleton<GameInstance> {

private SqliteConnection _conn4;
private SqliteConnection _conn5;

public void Start()
{
     CreateTable();
}

public void CreateTable()
{
     _conn4 = new SqliteConnection(DatabaseAccess.ConnDatabaseStringUsers);
     _conn4.Open();

     try
     {
          SqliteCommand cmd4 = new SqliteCommand("CREATE TABLE IF NOT EXISTS Users ('ID' INTEGER, 'FileNumber' TEXT, 'FamilyName' TEXT, 'Name' TEXT, 'ParentFolderID' INTEGER, 'AvatarSex' INTEGER, 'Role' INTEGER)", _conn4);
          cmd4.ExecuteNonQuery();
     }

     catch (UnityException e)
     {
       Debug.LogError(e);
     }

     finally
     {
       _conn4.Close();
     }

     CopyTable();
}

public void CopyTable()
{
     _conn5 = new SqliteConnection(DatabaseAccess.ConnDatabaseStringUsers);
     _conn5.Open();

     try
     {
          SqliteCommand cmd5 = new SqliteCommand("SELECT * INTO Users IN 'TSDemo.db' FROM Users", _conn5);
          cmd5.ExecuteNonQuery();
     }

     catch (UnityException e)
     {
          Debug.LogError(e);
     }

     finally
     {
          _conn5.Close();
     }
   }
 }

I expect the data from the table Users from the first .db database to be copied to the table Users from the second .db database, but it's not the case. However, Unity does not throw me any error. The table is being created, but data is not populated into it.

manpreet 3 years ago

Since SQLite doesn't support select into, you could either use create table as select if the table doesn't exist:

create table dest as select * from source

Or insert into select if the table exists or you create it:

insert into dest select * from source