Michael Miller wrote: > I apologize if this is a double-post; I just got approved for the mailing > list, and I can't find the older message in the archives, so I'm reposting > it. > > I have a table with two columns, the first with a string and the second with > an integer. > > > Given a set of input strings, I want to perform this operation 50,000+ times > preferably in a single transaction: "If the string doesn't exist in the > table, create a new row with the string in the first column and 1 in the > second column. If the string does exist in the table, increment the second > column by 1" > > > What I am doing now is using the UPDATE statement, and checking (via C#) the > number of rows affected, and if this is zero, running an insert statement. > Doing this 50,000 times, without an encapsulating transaction, is an > expensive operation. > > > Is there any way to encapsulate this into SQL so that no outside interaction > in C# is needed to perform this operation, and so I can put 50,000 of these > into a transaction and commit all at once? > > > Thanks, > > Mike > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >
See this thread from last week http://article.gmane.org/gmane.comp.db.sqlite.general/35829/match=easy+question+concerning+c%2b%2b+sqlite3 This exact issue was discussed. You can prepare the query once, and execute it 50K times in a transaction to quickly enter all your words. HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
