[thelist] SQL: finding "new" records in a table

[Fred Jones]

Judah McAuley wrote:
> I'm trying to do the heavy lifting at the database level. When I have 
> the csv file, I transfer it over the network to another machine. The 
> files aren't huge, so I'm not terribly worried about that. But then I 
> have to read the csv file in and loop over it, checking to see if each 
> is a new appointment (do an insert) or an existing appointment (do an 
> update). I'd like to minimize the size of the file I'm looping over for 
> efficiency sake and I'd also like to minimize the number of updates I'm 
> doing.

I would consider connecting directly to the main DB yourself every hour 
and loop through an SQL result set, instead of the CSV.

> What's the most efficient way to take care of that? Right now I'm just 
> doing an update if the appointment exists in the destination database. 
> That's not bringing the system to its knees or anything. But as it 
> grows, I'm worried that too many updates to the same table will cause 
> contention locks and slow the system down, so I'd like to minimize the 
> number starting now before it gets bad.

I am no expert over here, but I would suggest that the only alternative 
is of course to compare the records, that is the CSV record versus your 
DB record, to see if an update is necessary or not.

Fred