InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 23:07:33 GMT20http://www.sqlservercentral.com/Forums/Topic1412749-391-1.aspx[quote][b]Jason-299789 (1/29/2013)[/b][hr]Demon correct, but you can use the $Action on the output statement will show the action that was performed for that row of data.[/quote]Exactly Jason ; That ought to be the requirement. This is the formatted code with delete and case statement, not tested , but should work fine..[code="sql"]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; [/code]Tue, 29 Jan 2013 06:54:05 GMTdemonfoxhttp://www.sqlservercentral.com/Forums/Topic1412749-391-1.aspxDemon correct, but you can use the $Action on the output statement will show the action that was performed for that row of data.Tue, 29 Jan 2013 06:19:55 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1412749-391-1.aspx[quote][b]Lokesh Vij (1/29/2013)[/b][hr]Using merge (as pointed out by demonfox) you can easily user INSERTED.* and DELETED.* (as mentionded by Dwain) after MERGE in order to capture, columns and values which are being updated/inserted/deleted.[/quote]Yes and Inserted.col1 , Inserted.col2 woudl work for new data updated or inserted ; if you are handling delete also in the Merge statement.then it gets a little difficult to handle insert , delete and update data .As Insert and Update can be captured by Inserted.* ; but the deleted require Deleted.* , and in the merge statement there is only one Output clause allowed.In that case you may have to consider using cases by identifying $action property.Tue, 29 Jan 2013 06:00:29 GMTdemonfoxhttp://www.sqlservercentral.com/Forums/Topic1412749-391-1.aspxUsing merge (as pointed out by demonfox) you can easily user INSERTED.* and DELETED.* (as mentionded by Dwain) after MERGE in order to capture, columns and values which are being updated/inserted/deleted.Tue, 29 Jan 2013 05:10:11 GMTLokesh Vijhttp://www.sqlservercentral.com/Forums/Topic1412749-391-1.aspxTry this one , It might help ;[code="sql"]Merge into desttable as targetusing (select col1, col2,col3,col4,col5 from sourcetableexceptselect col1, col2,col3,col4,col5 from desttable) as sourceon target.col1=source.col1 -- (use primary key )when matched then update set target.col1=source.col1,.......when not matched bye target then insert (col1,col2,col3,col4,col5) values (source.col1,.....)[/code]merge statement to update or insert or delete the records.Tue, 29 Jan 2013 03:39:06 GMTdemonfoxhttp://www.sqlservercentral.com/Forums/Topic1412749-391-1.aspxSQL 2008 R2 - Change Tracking may help you...http://msdn.microsoft.com/en-us/library/cc280462(v=sql.105).aspxTue, 29 Jan 2013 01:07:31 GMTSuresh-209155http://www.sqlservercentral.com/Forums/Topic1412749-391-1.aspxTwo ways I know of to do this:1. In an UPDATE trigger, you can query UPDATE() function ([url]http://msdn.microsoft.com/en-us/library/ms187326.aspx[/url]) to tell you if the column was SET by the UPDATE statement. Unfortunately, it does not tell you whether it changed or not (but you could test this by comparing the values for INSERTED.column vs. DELETED.column using those pseudo tables.2. Use an OUTPUT clause of UPDATE to dump the updated results to a temporary table. I believe you can also reference INSERTED.* and DELETED.* psuedo tables there to dump all columns into the temp table. Then you have to query back from the temp table to see what was changed.Tue, 29 Jan 2013 00:01:11 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1412749-391-1.aspxHI ,I have table EMP(source), in this i have 5 columnswe don't know which column was updated in the sourcebut i want load to destination table with updated data onlyhow to write update statement for this requirementplz help meRegardsArjunMon, 28 Jan 2013 22:03:20 GMTArjun123