Hi,
I need to update periodically a sqlite table with data from a second sqlite table (identical structure).
Is it possible to do something like this? :
INSERT INTO file2.TableData (index, col1, col2, col3, col4)
SELECT index, col1, col2, col3, col4
FROM file1.TableData
WHERE index NOT IN (SELECT index FROM file2.TableData);
Thank you in advance
Are you an IT Pro with some training on DBs and data structures knowing exactly what you want? I ask that because I understand your intention but I don’t understand your contents and possible data conflicts. People without proper training learn the hard way about data conflicts and data synchronization.
Maybe you could explain a bit more what you are wanting to achieve? Or you may learn now something.
Let me tell you a problem I saw few years ago, someone requested something like what you want and I denied to give them what they wanted as they wanted. They had a distributed offline data acquisition, where sales people could contact some people and request some data from them generating 1 very simple person record, like [rowid, name]; and many data recs as [rowid, person_id, date_time, data], many times along multiple days.
This very simple system (made by the owner years before) was already in use, and…it was a single-user system. One user only, an operator, who knew all those people providing the data by name, so they used just the name as the identifier, and received new infos on paper to type, then the operator located the proper “person” record, and then he entered new data when new forms arrived. Like a sheet for “Samuel Quinn”, “A2345”, “B23452”; “Allison jones”, “F238447”, “J28848”, “P28882”
Then someone thought “we could make an exact offline data entry, people could collect the data, then later we just merge all data automatically!”. Can you spot the problem? 
Let me say another info, people could write the name freely in the form, sometimes “Samuel Quinn” could appear as “Sam Quinn” or “Samuel Queen”, but the operator fixed that at typing time (or not) I’ve found cases like Samuel Nicholas Quinn and Samuel Quinn coexisting but they were the same person. (The names and data here are fictional)
There were more problems than just that, I just said part of the problems to you understand my concerns.
Periodically, I download a db from cloud. This db is generated from a device. So I need to add to a offline db the new data. The structure is very simple. 5 column with number, not other. I think that solution is attach database, but I don’t know exatly what is the solution.
I’m not a IT Pro. I use Xojo 2016R4.1
Thank you
Yes.
Is it what you need? No.
Why?
In file1, you have 4 records , index 1,2,3,4
In file2, you have 3 records , index 1,2,3
So you add a copy of index 4
What you do not know is that in File1, the contents of col2 and 3 no longer match what exist in file2
You need to match those too.
One heavy approach is to assume that the downloaded one is up to date and just replace the one you have with a complete copy
This is an approach that works only if the remote database being copied is the only one receiving updates. If both databases can receive updates, then one needs to put in place a far more sophisticated approach. Maintaining master data is a deceivingly complex process. I have for example seen very poor quality customer master data in a leading ERP system. Master data governance rules are essential and need to be implemented above else, even in small scale systems. To this end, a thougough process analysis must be done. Once rules and processes are determined and agreed upon by stakeholders, then we can design suitable a technical solution.
You want to implement “incremental updates” (insert new data only). That demands finding the differential set. As we don’t know what we are talking about, that’s a task for you.
You have two databases A and B. A (incrementally updated) contains all the historical data over time, B (dynamically updated) contains some variant of “the same data” (A is updated), or the “same+new content” (B received new content), or “part of the same+new content” (updated B inserting new and deleting old data). You need a way to detect just “the new content” from B and construct the “incremental data set” C to be sent to incrementally insert the new data into A.
If you can find C correctly, this can be done, if not, you can’t.
We were talking about UUIDs as primary keys other day, your case seems one of those places where using it should be ideal (finding sparse differences).
The problem taht you say is solved with: WHERE index NOT IN (SELECT index FROM file2.TableData);
Best Regards
I did not say it solved the problem.
I said it was not good enough.
It will add any records that are missing, but it will ignore any records that have been deleted or changed in the file2 db
Rick, you understand the problem. I solved (work fine for me) in this way:
Dim FinalSql as string = "INSERT INTO DataGrpData (IndexID, CollectTime, ID, NCARD, IRRIG, RIMB, CRED) " + _
"SELECT IndexID, CollectTime, ID, NCARD, IRRIG, RIMB, CRED " + _
"FROM TempDB.DataGrpData " + _
"WHERE IndexID NOT IN (SELECT IndexID FROM DataGrpData);"
if App.Db.AttachDatabase(App.DB2.DatabaseFile, "TempDB") = true then
MsgBox "OK database Connesso, pronti per il trasferimento dati"
App.DB.SQLExecute (FinalSql)
end if