Copy between Databases

if have two SQLite databases… one is an “in-memory” and the other is a physical one.
both have identical schemas… how do I create an SQL statement to copy between them

Basically the process flow I need is this.

  • copy data from physical database to in-memory database
  • allow the user to do their thing… which may include adding records, changing records, deleting records, but NOT adding more tables (or deleting tables)
  • copy data from in-memory to physical (ONLY IF THE USER WISHES TO PERMANENTLY SAVE THEIR CHANGES)
  • at any time the user may also wish to “REVERT” to last saved copy

so somehow I need something like

SQL="INSERT INTO physcialDB.tablename SELECT * from inmemory.tablename"

I think you want to use the AttachDatabase method. In your case, I think you’d attach the physical database to the in-memory one.

Haven’t tried it though. See the Language Reference and this:

https://www.sqlite.org/lang_attach.html

I do a similar thing for one of my apps.
I use an in-memory database as my project area. When the user hits Save, it uses the db.backup to save the database to the user’s computer.

The nice thing is, that you can show a progress bar to see the progress of the save operation. That is helpful if your database has a lot of data and the user has no clue what is going on, while staring at the “hourglass” cursor…

I use this … U can invert the flow…

dim sqlcreate as string
dim cursore as string = "yourtable"
if not inMemorydb.Connect() Then
    MsgBox("Errore durante l'apertura del db virtuale : " + inMemorydb.errorMessage)
    exit sub
end if
sqlcreate = "DROP TABLE IF EXISTS " + cursore + ";" + EndOfLine 
inMemorydb.SQLExecute(sqlcreate)
if inMemorydb.error then 
    MsgBox("Error during delete temp table " + cursore + " : " + inMemorydb.errorMessage)
    exit sub
end if

sqlcreate = "CREATE TABLE " + cursore + " (" + EndOfLine
for cursoreidx as integer = 0 to rsreport.FieldCount -1
    dim nomecolonna as String
    nomecolonna = allowedfilter(rsreport.IdxField(cursoreidx +1).Name,"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890")
    if cursoreidx <> 0 then sqlcreate = sqlcreate + ","
         select case rsreport.ColumnType(cursoreidx)
    case 0,255,17,16,15 // testo 
	 sqlcreate = sqlcreate + nomecolonna + " TEXT " + EndOfLine
    case 4,5,18 // testo
         sqlcreate = sqlcreate + nomecolonna + " TEXT " + EndOfLine
    case 1,2,3,12,14,19 // intero
         sqlcreate = sqlcreate + nomecolonna + " INTEGER " + EndOfLine
    case 6,7,11,13 // double
	 sqlcreate = sqlcreate + nomecolonna + " CURRENCY " + EndOfLine
    case 8 // data
	 sqlcreate = sqlcreate + nomecolonna + " DATE " + EndOfLine
    case 9 // ora
	 sqlcreate = sqlcreate + nomecolonna + " TEXT " + EndOfLine
    case 10 // timestamp
	 sqlcreate = sqlcreate + nomecolonna + " TEXT " + EndOfLine
    case else
	 sqlcreate = sqlcreate + nomecolonna + " TEXT " + EndOfLine
    end select
    nomecolonna = ""
next
sqlcreate = sqlcreate + ");"

inMemorydb.SQLExecute(sqlcreate)
if inMemorydb.error then 
     MsgBox("Errore durante la creazione della tabella temporanea " + cursore + " : " + inMemorydb.errorMessage)
     exit sub
end if
						
// popolo il db virtuale
while not rsreport.eof
    dim row as new DatabaseRecord
    for cursoreidx = 0 to rsreport.FieldCount -1
	dim nomecolonna as String
	nomecolonna = allowedfilter(rsreport.IdxField(cursoreidx +1).Name,"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890")
	select case rsreport.ColumnType(cursoreidx)
	case 0,255,17,16,15 // testo 
		row.Column(nomecolonna) = rsreport.IdxField(cursoreidx +1).StringValue
	case 4,5,18 // testo
		row.Column(nomecolonna) = rsreport.IdxField(cursoreidx +1).StringValue
	case 1,2,3,12,14,19 // intero
		row.IntegerColumn(nomecolonna) = rsreport.IdxField(cursoreidx +1).IntegerValue
	case 6,7,11,13 // double
		row.CurrencyColumn(nomecolonna) = rsreport.IdxField(cursoreidx +1).CurrencyValue
	case 8 // data
		row.DateColumn(nomecolonna) = rsreport.IdxField(cursoreidx +1).DateValue
	case 9 // ora
		row.Column(nomecolonna) = rsreport.IdxField(cursoreidx +1).StringValue
	case 10 // timestamp
		row.Column(nomecolonna) = rsreport.IdxField(cursoreidx +1).StringValue
	case else
		row.Column(nomecolonna) = rsreport.IdxField(cursoreidx +1).StringValue
	end select
    next
    inMemoryDb.InsertRecord(cursore,row)
    if inMemorydb.error then 
	MsgBox("Errore durante l'inserimento dei dati nella tabella virtuale " + cursore + " : " + inMemorydb.errorMessage)
	exit sub
    end if
    rsreport.MoveNext
wend

use currency instead of double because decimals are limited to 4 and during financial calculations NOT SURVEY ERRORS, whereas if I use double waste in decimal that puzzle me …

Excuse, I forgot the allowfilter function …

		dim i as integer
		dim risultato as string
		for i = 1 to len(sorgente)
				if InStr(caratteri, sorgente.Mid(i, 1)) > 0 then
						risultato = risultato + sorgente.Mid(i, 1)
				end if
		next
		return risultato

Massimiliano, is better and simpler use the backup command (from or to a memory db)

Yes Antonio, but if you have two different tipe of database eg: Mysql and Sqlite ?