title: sql server merge with multiple insert when not matched
body: <p>I'm using MERGE in my query and i'm making INSERT on clause WHEN NOT MATCHED THEN, but then i would like to get the inserted row identity and make another INSERT to some other table. Query for now is:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[BulkMergeOffers]
  @data ImportDataType READONLY
  AS
  SET NOCOUNT ON;
  DECLARE @cid int = 0
  MERGE dbo.oferta AS target
  USING @data AS source 
  ON    (target.nr_oferty = source.nr_oferty)
  WHEN NOT MATCHED THEN 
  INSERT (nr_oferty,rynek,typ_transakcji, typ_nieruchomosci,cena,powierzchnia, rok_budowy, wojewodztwo, miasto, dzielnica, ulica, opis, wspolrzedne, film, zrodlo, KontaktStore, data, forma_wlasnosci, stan_techniczny, liczba_pokoi, liczba_peter, pietro, material, kuchnia, pow_dzialki, typ_dzialki, woda,gaz, prad,sila, przeznaczenie,lokal_dane)
        VALUES (source.nr_oferty,source.rynek,source.typ_transakcji,  source.typ_nieruchomosci,source.cena,source.powierzchnia, source.rok_budowy, source.wojewodztwo, miasto,    source.dzielnica, source.ulica, source.opis, source.wspolrzedne, source.film, source.zrodlo, source.KontaktStore, source.data, source.forma_wlasnosci, source.stan_techniczny, source.liczba_pokoi,     source.liczba_peter, source.pietro, source.material, source.kuchnia, source.pow_dzialki, source.typ_dzialki, source.woda,source.gaz, source.prad,source.sila, source.przeznaczenie,source.lokal_dane); 
  </code></pre>
  
  <p>So as you see i need to insert some values to the target table based on source data, then i need to take the insert identity and insert it into another table but also based on some source data, so something like that, just after the first insert:</p>
  
  <pre><code>SET @cid = SCOPE_IDENTITY();
  if source.photo is not null
  begin
  insert into dbo.photos(offerID, file) values (@cid, source.photo);
  end
  </code></pre>
  
  <p>But i can't assemble it, a have no access to the source no more, also if statement show error : </p>
  
  <blockquote>
    <p>"the multi-part identifier
    source.photo can not be bound"</p>
  </blockquote>
  
  <p>but it is there. Just for clarity ImportDataType is a table-valued parameter.</p>
  
  <p>Please HELP</p>