Exactly Jason ; That ought to be the requirement. This is the formatted code with delete and case statement, not tested , but should work fine..

CREATE TABLE #author2 (cola VARCHAR(50),colb VARCHAR(50), action_flag VARCHAR(10) )

CREATE TABLE dbo.author1

( cola VARCHAR(50), colb VARCHAR(50) )

CREATE TABLE dbo.authors

( au_fname VARCHAR(100), au_lname VARCHAR(100)

)

MERGE INTO authors AS target

using (SELECT cola,

colb

FROM author1

EXCEPT

SELECT au_fname,

au_lname

FROM authors) AS source

ON target.au_fname = source.cola

WHEN matched THEN

UPDATE SET target.au_lname = source.colb

WHEN NOT matched BY target THEN

INSERT (au_fname,

au_lname)

VALUES(source.cola,

source.colb)

WHEN NOT matched BY source THEN

DELETE

output ( CASE

WHEN $action = 'DELETE' THEN deleted.au_fname

ELSE inserted.au_fname

END )au_fname,

( CASE

WHEN $action = 'DELETE' THEN deleted.au_lname

ELSE inserted.au_lname

END )colb,

$action

INTO #author2;