How can I ignore an sqlite3 insertion failures to allow a tcl script to continue after the failure?

Stripped down, what I trying to do it the following

set selectQuery "SELECT col1, col2 FROM tableName" 

db1 eval $selectQuery {
   set insertQuery "INSERT INTO tableName VALUES($col1, $col2)"
   db2 eval $insertQuery {
      # Do trivial stuff to show progress
   }
}

which basically copies the content of db1.tableName to db2.tableName. The problem is that in my case, most of the content of db1.tableName already exists in db2.tableName. So basically, I just want to copy what doesn't exist yet so I thought I would just insert everything and let the inserts fail when the data's already there. This doesn't work because the whole script stops as soon as one insert fails. I tried using catch to ignore the failure and allow the script to continue, but I haven't been successful. Any ideas?

Also, there ma开发者_运维百科y be a better way to copy the contents of db1.tableName to db2.tableName without copying what's already there... Any help will be appreciated!

Shawn

P.S If you have ideas for a better title, that would be useful as well..

Sqlite has a INSERT OR REPLACE INTO construct which may be useful in this case.

http://www.sqlite.org/lang_insert.html

While I'm not familiar with your database api commands, so I can't comment on how you're looping throught the query results, but I can suggest a few things.

First, try catch again. I always find the usage a bit strange, but eventually you get used to it. Here's an example:

if { [catch { db2 eval $insertQuery} errmsg] } {
    #There was an error - it is stored in $errmsg"
} else {
    #success! Congratulations.
}

In your case, I imagine you just want to ignore any insert errors, so you can simply do:

set selectQuery "SELECT col1, col2 FROM tableName" 

db1 eval $selectQuery {
   set insertQuery "INSERT INTO tableName VALUES($col1, $col2)"
   if { ![catch { db2 eval $insertQuery} errmsg] } {
      # Do trivial stuff to show progress
   }
}

You could also try doing the query first and putting the query results in a TCL list (or list of lists) - then do a foreach on the list, doing the inserts. Sometimes databases can be funny about mutating data, although it doesn't look at all like that is happening in your case.