tsql - SQL Server: Can Merge Insert Conditionally or Update a Source Table? -

tsql - SQL Server: Can Merge Insert Conditionally or Update a Source Table? -

i using sql server 2008 , synchronizing info between 2 tables this:

merge stock target using stockhistory source on target.srno = source.srno when matched # update record target table when not matched # insert record target table when not matched source # update source table - current record

question 1: insert record conditionally "when not matched then" fire. e.g. in case source table column refsrno_stockcompany null should not insert in target.

question 2: in case target table "stock" not matched source "when not matched source then" fire , should update source table --> issoldout = 'yes'.

kindly suggest how can accomplish this.

thanks

for question 1: add together and status when not matched clause:

when not matched , source.[refsrno_stockcompany] not null # insert record target table

for question 2: cannot done straight target table can modified. however, can done indirectly in sec step. trick utilize output clause capture temp table records updated (and, rather utilize $action distinguish between insert , update, grab deleted table which, definition, never populated on insert operations) . utilize list bring together when doing update on source table filter out in list. records in temp table records not need updated since matched. records in source table not in temp table represent of records have matched on when not matched source condition. of course, records inserted via when not matched clause show null records in temp table (as there nil in deleted table insert operation), , can filtered out of update.

-- before merge create table #updatedrecords (srno int); begin seek begin tran; merge ... -- add together end of merge query output deleted.srno #updatedrecords (srno); -- optionally after merge delete upd #updatedrecords upd upd.srno null; -- after merge update sh set sh.issoldout = 'yes' stockhistory sh not exists ( select upd.srno #updatedrecords upd upd.srno = sh.srno ); commit tran; end seek begin grab rollback tran; declare @error nvarchar(4000) = error_message(); raiserror(@error, 16, 1); end catch;

and if want ensure consistency of these 2 statements beingness single operation have been when using merge (had worked), wrap merge , update in begin tran , commit tran.

sql-server tsql