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_D ATE 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