0

Lets say I have 2 database files, main and new database. Everytimes when new database file is released, Users need to update main database from new database.

  1. If certain row is found e.g. CustomerName is same, then I need to update old records in main database.
  2. If it is not found, then I need to insert new record into main database.

What I had tried is select all rows from new database, then go through row by row by using reader.Read(). Inside the loop I will check if update sql return -1, then I will execute the insert sql.

The above approach was having performance issue, as it need to go through every rows to execute update or insert. My proposed solution is to attach new database into main database, then carry out "INSERT or REPLACE table SELECT * FROM newdatabase.table".

But soon I realized a problem. I do not actually have a primary key in table, or the primary key does not help to decide INSERT or REPLACE. This is due to some specific customer requirement. In this case, I cannot use INSERT or REPLACE without primary key e.g. column to compare (CustomerName) is not unique or primary key.

Please let me know if you have any better solution, thank!

Fernando Tan
  • 634
  • 4
  • 14
  • can you use a stored procedure? – logixologist May 11 '16 at 17:50
  • unfortunately sqlite does not have stored procedure :( – Fernando Tan May 11 '16 at 17:51
  • You should almost always have a primary key in your table, [especially if you want to do inserts](http://stackoverflow.com/questions/831850/should-a-database-table-always-have-primary-keys). – Lews Therin May 11 '16 at 17:51
  • Try looking at this and see if it will help. Its an alternative to a SQL Sproc for SQLLIte. http://stackoverflow.com/questions/3335162/creating-stored-procedure-and-sqlite – logixologist May 11 '16 at 17:53
  • Sorry for misleading here, but I should edit my sentence here, the primary key is just simply auto increment id, because we might have multiple customer name and id in table, so the primary key does not help to decide INSERT or REPLACE – Fernando Tan May 11 '16 at 17:53
  • Also instead of using a DataReader and go through each row looking to see if it exists, why not use a DataAdapter and actually do a select, looking for the critieria. – logixologist May 11 '16 at 17:54

0 Answers0