Update/Insert with constraint

[chrisbo]

I have two tables in SQL Server 2005: ImagesMain and ImagesNew. They have the same structure:

FILENAME     WIDTH    HEIGHT    LASTMODIFIED
00328.JPG    757    275     2007-05-08 09:40:25
01_806.jpg     567        423         2007-10-03 10:16:57

ImagesMain is a listing of all images on a server.

ImagesNew contains a listing of a subset of the images on the server. It contains:
-Items that exists in the main table
-Items that exists in the main table but has changed since the last update
-New items that don't exist in the main table

I'd like to update the main table in the following manner:

If row exists with current filename
    if new date > old date
        update row
else
    insert row

Any tips on how I could accomplish this? Cursors?

/Christian

[ck9663]

Cursor is your last option.

It might be faster if you do this using 2 queries. An update query, followed by an INSERT query. If you do the INSERT first, your update query will kick-in (although no update will actually be done since LAST_MODIFIED_DATE are equal on both rows) and will take more processing time. You don't need to compare the newly inserted record since they are new in the first place.

-- CK