Copying a TABLE (or a db file contents) ?

Databases
1

Do not ask me why *, but before today’s nap, I coded a Method to copy a TABLE (with standard text) from an existing (and know) SQLite Data Base file, into a .sqlite to be created file into the same TABLE name and Fields.

Now that I awoke, a question arise in my brain:

a. Why not doing a file copy ?
b. Isn’t there’s a faster way to copy a TABLE and its contents ?

Nota:
I do not say that often, but I usually do my reseach work before asking question(s) here.As an example, I spend (wasted ?) three hours yesterday evening reading the SQL for dummies searching … where Allen talk about reading data from a RecordSet (IdxField) to compare how Xojo do that…
I used the pdf and never found anything. (again, do not ask me why: I forgot).

  • I coded that in an Import / Export SQLite DB window utility (as a test project to be included in a far larger project).
2

a. FileCopy will copy the DATABASE not just a single table
b. you could copy a table using

CREATE newTABLE as SELECT * from oldTABLE

but that will copy only the data, but not any Primary Key, Default Values or other constraints… for that you need to Create a new table with all that, and then INSERT the data

Tadpole (my SQLite manager coming soon)… has wizards to do all those kind of things and much much more

3

If it is just Sqlite, you can simply use the ATTACH DATABASE statement --> https://sqlite.org/lang_attach.html

Assuming I have an sqlite database called original with two fields (one integer, one text) and wanna copy it:

dim db as new sqlitedatabase
		
db.databasefile = getfolderitem("I:\\playground\\original.sqlite")
		
if db.connect then
	db.SQLExecute("ATTACH DATABASE 'I:\\playground\\copy.sqlite' AS 'thecopy'")
	db.SQLExecute("CREATE TABLE thecopy.testtable (`Field1` INTEGER, `Field2` TEXT);")
	db.SQLExecute("INSERT INTO thecopy.testtable SELECT * FROM main.testtable")
	if db.error then
		msgbox(db.ErrorMessage)
	end if
else  
	msgbox ("something went wrong")
end if

The schema name of the originally attached database is always “main”, so use it to refer to it.

4

If you don’t care about any original constraints (see my above post) then it is just as easy to

	db.SQLExecute("ATTACH DATABASE 'I:\\playground\\copy.sqlite' AS 'thecopy'")
	db.SQLExecute("CREATE TABLE thecopy.testtable as SELECT * FROM main.testtable")
5

How about something like this to copy the table schema:

rs = db.SQLSelect("SELECT sql from sqlite_master where type = 'table' AND tbl_name = testtable")
if rs <> nil and rs.recordcount > 0 then  newdb.SQLExecute(rs.field("sql").stringvalue)
6

Besides the fact that that will fail?
If there is an entry in SQLite_Master for “testtable” and you execute the SQL that you extracted… you will be trying to create “testtable” AGAIN… and since it already exists… .the query will fail

7

Note the newdb.SQLExecute(...)
This assumes a prior statement like Dim newdb as SQLiteDatase = new SQLiteDatabse

8

IF you were to copy to another database, and IF you had two database object (as opposed to an Attached database), then yes that will work

9

Right you would need two database objects. They could be attached using newdb.AttachDatabase

If writing to the same db you could do something like this:

  rs = db.SQLSelect("SELECT sql from sqlite_master where type = 'table' AND tbl_name = testtable")
  if rs <> nil and rs.recordcount > 0 then
    db.SQLExecute(rs.field("sql").stringvalue.replace(" testtable", " newtablename"))
  end if
10

Thank you all for your replies.

To avoid creating two times the same TABLE (its possible to attempt, but I forgot how SQLIte / Xojo react), use If Exists or as I’ve done:

CREATE TABLE IF NOT EXISTS …

Also:
I stored in a Service Table the TABLE Fields Contents (the Column Names for the Listbox Header…).

I forgot why I started that “feature”, but I continued it in a new Window that can do Import / Export of my original Data Base file. The Import / Export “convert” the data from a list of formats into another list of formats (CSV, JSON, TEXT, SQLite).

I also added an Export of a Table with FIrst Name / Family Name / Photo ID from my main file: I will use it from a different file (10MB for 110 entries…).

And a simple tool (handly tool) on hand is far better than a full file recreation.

About SQLite (SQL) refinements like the ones talks about: I do not use them at all (only Unique ID, but as it was stated many times, this is … mandatory !).

Last question:
I am using DataBaseRecord in this window’s code related to DB. My first feeling is that this is… the fastest way to use File IO with Data Bases (vs INSERT…). Am I right ?
Note: I have around 2,000 Records, but the 110 entries with Photo ID copy was also very fast !