sql server merge with multiple insert when not matched

Question

Hi.

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:

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); 

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:

SET @cid = SCOPE_IDENTITY();
if source.photo is not null
begin
insert into dbo.photos(offerID, file) values (@cid, source.photo);
end

But i can't assemble it, a have no access to the source no more, also if statement show error :

"the multi-part identifier source.photo can not be bound"

but it is there. Just for clarity ImportDataType is a table-valued parameter.

Please HELP

Answer 1

If you don't need the WHEN MATCHED part of the MERGE statement in your query, there's no real reason to use MERGE. You could use INSERT with an outer join or NOT EXISTS statement.

In either case, you can use the OUTPUT clause to retrieve the inserted identity value an pass it on to a second query.

I've extended your example:

<stored procedure header - unchanged>

--declare a table variable to hold the inserted values data
DECLARE @newData TABLE
(nr_oferty int
,newid int
) -- I'm guessing the datatype for both columns

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)
OUTPUT inserted.nr_oferty, inserted.<tableId> INTO @newData; 
-- replace <tableId> with the name of the identity column in dbo.oftera

insert into dbo.photos(offerID, file)
SELECT nd.newid, pt.photo
FROM   @data AS pt
JOIN   @newData AS nd
ON     nd.nr_oferty = pt.nr_oferty
WHERE  pt.photo IS NOT NULL