sqlite - merge two databases

BlitzMax Forums/Brucey's Modules/sqlite - merge two databases

UNZ	(Posted 2013) [#1]

If have two databases that I want to combine. Let's call 'em dbFrom and dbTo.

example:

SuperStrict
Framework brl.standardio
Import bah.dbsqlite

Local db:TDBConnection = LoadDatabase("SQLITE", "dbTo.db")

If db.isOpen()
	Local dbFromName$ = AppDir + "/dbFrom.db"
	Local table$ = "bmx_keywords"
		db.executeQuery(..
			"ATTACH '"+dbFromName+"' AS toMerge;" + ..
			"INSERT OR REPLACE INTO " + table + " SELECT * FROM toMerge." + table)
	If db.hasError() Then DebugLog db.error().toString()
	DebugLog "done"
EndIf

The problem is that this is not working. Nothing happens in dbTo.
Im a newbie to sql but I think the query is correct, isn't it?

Henri

(Posted 2013) [#2]

Hello,

something like this works:

Strict

Import bah.dbsqlite

Local db:TDBConnection = LoadDatabase("SQLITE", "source.db")

If db.isOpen()
	Local sql:String
	
	sql = "ATTACH 'target.db' AS target"
	db.executeQuery(sql)
	If db.hasError() Then DebugLog db.error().toString()
	
	sql = "INSERT Or REPLACE INTO target.table_1 SELECT * FROM table_1"
	db.executeQuery(sql)
	If db.hasError() Then DebugLog db.error().toString()
	
	DebugLog "done"
	db.close()
EndIf

...if you want to copy a table between databases (both tables with similar structures)

-Henri

UNZ	(Posted 2013) [#3]

Ok, that works.
But isn't it possbile to have the target db open and attach the source?

Henri

(Posted 2013) [#4]

I don't see why not. If the table names are not unique you have to specify which database the table comes from.

-Henri