Guys, I think I’ve asked this question in the past but got pulled away and never got back to it, but now I have some more time. My question has to do with general SQL usage.
Let’s say I have a database with a list of computers and some fields such as computerName(PK), customerName, phone, and so on.
Let’s say I have a folder where a file or files may exist named computerName.txt. In that file(s) will be the fields I have above. The file may be a new name not yet in the database or it may be an existing name with some of the fields having updated values. After I finish with the file I delete it. I then check back again hourly to see if there are any new files in the folder.
Now I want to scan that folder, read each file, and put it into the database.
My problem is that all the tutorials I have seen for INSERT assume there are no entries with the PK already in the database. How do I check for the record and if it’s in the database check the fields to see if any of the values have changed and UPDATE them? If the record does not exist then INSERT the new record into the database.
I imagine this is done with subqueries right? Can you guys give me some psuedo to show me how it’s done? Thanks.
Thanks for the replies guys. I never knew ON DUPLICATE KEY UPDATE existed. Does that exist in Microsoft SQL and is that normally how things are updated and inserted?
You see right now I have a Microsoft SQL 2005 Express database that came with a product that we purchased that holds information about the computers we support. I then have a bunch of files that hold customer information that the commercial product does not have. I want to get the file data into a database but I’ve never used INSERT, UPDATE, DELETE in real world scenarios before. I use Vbscript to do SELECT statements from the MS database everyday, but now I want to get the file data into another database which probably will be MYSQL.
If I stick with Microsoft SQL since the SQL server is already in place, back to my original question, how would you handle what I described originally?
For MSSQL, best bet is to either make a stored proc which handles the “INSERT OR UPDATE” logic, or do it in your client app with a multi-line SQL statement. This will keep it inside a transaction which should avoid the race condition in general.