sql server - SQL. How to insert one select into another select with the ignoring of duplicates and the with changing of Foreign Key in a copy -

original task: copy lines 1 document ignoring duplicates.

copy document minimum number document maximum number. assume there @ least 1 entry in header. when adding, skip (do not add) rows products exist. here's whole code:

create table tov (     ktov int primary key not null,     ntov varchar(max) not null,     sort varchar(max) not null ); go  create table dmz (     ddm date not null,     ndm int primary key not null,     pr int not null ); go  create table dms (     ktov int not null         foreign key references tov(ktov),     ndm int not null         foreign key references dmz(ndm),     kol int not null,     cena decimal(13,2) not null,     sort varchar(max) not null ); go  insert tov values (101, 'beer', 'light'), (102, 'beer', 'dark'), (103, 'chips', 'with paprika'); go  insert dmz values ('01.05.2014', 2, 1), ('01.05.2104', 3, 2), ('02.05.2014', 5, 2); go  insert dms values (101, 2, 100, 8.00, 'light'),  (102, 3, 80, 9.50, 'dark'),  (103, 5, 50, 6.50, 'with paprika'), (101, 2, 1, 10.00, 'light'),  (103, 3, 1, 8.50, 'with paprika'),  (101, 5, 2, 10, 'light'),  (102, 3, 1, 11.50, 'dark'),  (101, 2, 2, 10.50, 'light'),  (103, 5, 1, 8.60, 'with paprika'); go 

i'm stuck , can not find solution. there such pseudo-query:

insert (select * dms ndm = (select min(ndm) dms))  (select * dms ndm = (select max(ndm) dms)) on duplicate key update  

there such variant of query:

create view mindms1 select * dms1 ndm = (select min(ndm) dms1);  create view maxdms1 select * dms1 ndm = (select max(ndm) dms1);   merge  maxdms1 maxd      using mindms1 mind      on (maxd.ktov = mind.ktov , maxd.ndm > mind.ndm)          when not matched              insert (ktov, ndm, kol, cena, sort)              values (mind.ktov, mind.ndm, mind.kol, mind.cena, mind.sort); 

but works wrong. data coped mindms1. need copy maxdms1. , don't know how change ndm in copy(it foreign key) maxdms1.ndm

no need merge if operation insert only:

depending on columns determine duplicate, compare them in where of not exists() clause. example, comparing ktov , kol (ktov alone results in no rows inserted)

declare @min_ndm int, @max_ndm int; select @min_ndm = min(ndm), @max_ndm = max(ndm) dms;  insert dms (ktov, ndm, kol, cena, sort) output inserted.* select o.ktov, ndm=@max_ndm, o.kol, o.cena, o.sort dms o o.ndm = @min_ndm   , not exists (     select 1     dms     i.ndm = @max_ndm       , i.ktov = o.ktov       , i.kol  = o.kol     ) 

dbfiddle.uk demo

inserts following rows:

+------+-----+-----+-------+-------+ | ktov | ndm | kol | cena  | sort  | +------+-----+-----+-------+-------+ |  101 |   5 | 100 | 8.00  | light | |  101 |   5 |   1 | 10.00 | light | +------+-----+-----+-------+-------+ 

---

if want use merge, can use common table expressions target , source:

declare @min_ndm int, @max_ndm int; select @min_ndm = min(ndm), @max_ndm = max(ndm) dms;  ;with mindms (select * dms ndm = @min_ndm)     , maxdms (select * dms ndm = @max_ndm) merge maxdms t   using mindms s     on (t.ktov = s.ktov , t.kol = s.kol)   when not matched     insert values (s.ktov, @max_ndm, s.kol, s.cena, s.sort) output $action, inserted.*; 

dbfiddle.uk demo

returns:

+---------+------+-----+-----+-------+-------+ | $action | ktov | ndm | kol | cena  | sort  | +---------+------+-----+-----+-------+-------+ | insert  |  101 |   5 | 100 | 8.00  | light | | insert  |  101 |   5 |   1 | 10.00 | light | +---------+------+-----+-----+-------+-------+ 

---

some merge issues aware of:

• use caution sql server''s merge statement - aaron bertrand
• an interesting merge bug - paul white