title: How to use different sub queries with SQL MERGE
body: <p>Can we pass different sub queries in SQL MERGE for inserting and updating data?</p>
  
  <pre><code>  MERGE TableA AS SOURCE
      USING TbaleB AS TARGET
      ON (SOURCE.ID=TARGET.ID) 
      WHEN NOT MATCHED BY TARGET  
      THEN INSERT(COLUMN1, COLUMN2,COLUMN3 ) 
          SELECT * FROM TableC
      WHEN MATCHED 
      THEN UPDATE COLUMN1=SOURCE.Column1, COLUMN2=SOURCE.Column2,COLUMN3=SOURCE.Column3
     FROM Table4  
     INNER JOIN INNER JOIN Table5
      ON  Table4.ID=Table5.ID
       WHERE Table5.ID=1
  
  OUTPUT $action, inserted.*;
  </code></pre>
  
  <p>Is there any other option to do this?</p>
  
  <p>EDIT:
  Using below script to create tables, insert data, </p>
  
  <pre><code>CREATE TABLE TableA([ID] [int] IDENTITY(1,1) NOT NULL,
      ColumnA [nvarchar](50) NULL,
      ColumnB [nvarchar](50) NULL,
      ColumnC [nvarchar](50) NULL,    
  ) ON [PRIMARY]
  
  CREATE TABLE TableB([ID] [int] IDENTITY(1,1) NOT NULL,
      ColumnA [nvarchar](50) NULL,
      ColumnB [nvarchar](50) NULL,
      ColumnC [nvarchar](50) NULL,    
  ) ON [PRIMARY]
  
  Insert into TableA values('A','1','W')
  Insert into TableA values('A','2','X')
  Insert into TableA values('B','1','Y')
  Insert into TableB values('A','1','U')--To be update
  Insert into TableB values('B','2','N')--New row to insert
  </code></pre>
  
  <p>Used below query to insert: </p>
  
  <pre><code>SELECT Source.* INTO #tempTable 
  FROM (SELECT row_number() OVER ( PARTITION BY ColumnA,ColumnB ORDER BY ColumnA,ColumnB ) Row_ID, * --using row_number to remove duplicate rows
  FROM  TableB) Source  
  LEFT OUTER JOIN TableA Target 
  ON  Source.ColumnA =  Target.ColumnA AND  Source.ColumnB =  Target.ColumnB 
  WHERE Row_ID=1 AND Target.ColumnB IS NULL  AND Target.ColumnB IS NULL  
  ALTER TABLE #tempTable drop column Row_ID, ID 
  
  INSERT INTO TableA(ColumnA,ColumnB ,ColumnC) 
  select * from #tempTable 
  </code></pre>
  
  <p>Used below query to update existing record:   </p>
  
  <pre><code>UPDATE Target SET Target.ColumnC =Source.ColumnC
  FROM TableA Target  
  INNER JOIN TableB AS Source
  ON  Source.ColumnA =  Target.ColumnA AND  Target.ColumnB =  SOURCE.ColumnB
  </code></pre>
  
  <p>And I need the output as </p>
  
  <pre><code>ID  ColumnA ColumnB ColumnC
  4   A   1   U
  5   A   2   X
  6   B   1   Y
  7   B   2   N
  </code></pre>
  
  <p>It is working fine, but for performance perpose, can we do the same by using MERGE?</p>