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

Question

Original task: Copy lines from one document to another with ignoring duplicates.

Copy from the document with the minimum number to the document with the maximum number. Assume that there is at least one entry in the header. When adding, skip (do not add) rows for products that already exist. Here's the 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 and I can not find a solution. There is such a pseudo-query:

INSERT INTO (select * from DMS WHERE NDM = (SELECT MIN(NDM) FROM DMS)) 
FROM (select * from DMS WHERE NDM = (SELECT MAX(NDM) FROM DMS))
ON DUPLICATE KEY UPDATE

There is also such a variant of the query:

CREATE VIEW MINDMS1
AS SELECT * FROM DMS1 WHERE NDM = (SELECT MIN(NDM) FROM DMS1);

CREATE VIEW MAXDMS1
AS SELECT * FROM DMS1 WHERE NDM = (SELECT MAX(NDM) FROM DMS1);

 MERGE  MAXDMS1 AS MAXD 
    USING MINDMS1 AS MIND 
    ON (MAXD.KTOV = MIND.KTOV AND MAXD.NDM > MIND.NDM) 
        WHEN NOT MATCHED THEN 
            INSERT (KTOV, NDM, KOL, CENA, SORT) 
            VALUES (MIND.KTOV, MIND.NDM, MIND.KOL, MIND.CENA, MIND.SORT);

But it works wrong. The data coped to the MINDMS1. But it is need to copy into MAXDMS1. And I don't know how to change ndm in copy(it is foreign key) to MAXDMS1.NDM

Raging Bull, thanks for your formatting. I am just looking how to do this — user8375976, Sep 10 '17 at 13:07
So horrible those table and column names. I must admit I stopped looking into this when I saw the names. Before mentioning the tables you talked of `documents`, `headers` and `products` and I expected to see a table `product` with a `product_id` or the like, but instead I see TOV, KTOV, NTOV, DMZ, DDM, NDM, .... whatever they mean. I suggest you change those names so as to get this readable. — Thorsten Kettner, Sep 10 '17 at 14:21

SqlZim · Answer 1 · 2017-09-10T14:34:08.497

No need for merge if this operation is insert only:

Depending on which columns would determine a duplicate, compare them in the where of a not exists() clause. For the example, I am comparing ktov and kol (ktov alone results in no rows inserted)

declare @min_ndm int, @max_ndm int;
select @min_ndm = min(ndm), @max_ndm = max(ndm) from DMS;

insert into dms (ktov, ndm, kol, cena, sort)
output inserted.*
select o.ktov, ndm=@max_ndm, o.kol, o.cena, o.sort
from dms o
where o.ndm = @min_ndm
  and not exists (
    select 1
    from dms i
    where i.ndm = @max_ndm
      and i.ktov = o.ktov
      and i.kol  = o.kol
    )

dbfiddle.uk demo

inserts the following rows:

+------+-----+-----+-------+-------+
| KTOV | NDM | KOL | CENA  | SORT  |
+------+-----+-----+-------+-------+
|  101 |   5 | 100 | 8.00  | Light |
|  101 |   5 |   1 | 10.00 | Light |
+------+-----+-----+-------+-------+

If you really want to use merge, then you can use common table expressions for your target and source:

declare @min_ndm int, @max_ndm int;
select @min_ndm = min(ndm), @max_ndm = max(ndm) from DMS;

;with mindms as (select * from dms where ndm = @min_ndm)
    , maxdms as (select * from dms where ndm = @max_ndm)
merge into maxdms as t
  using mindms as s
    on (t.ktov = s.ktov and t.kol = s.kol)
  when not matched then
    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 to be aware of:

Thanks a lot. May i ask please: why after this select "not exists ( select 1" you write "1"? — user8375976, Sep 10 '17 at 14:37
@user8375976 I use `select 1` out of habit. Both `exists()` and `not exists()` do not return rows, so you could use `select null` and `select 1`. Based on this article [EXISTS Subqueries: SELECT 1 vs. SELECT * - Conor Cunningham] (http://www.sqlskills.com/blogs/conor/exists-subqueries-select-1-vs-select/) using `select 1` will avoid having to examine any unneeded metadata for that table during query compilation. [EXISTS Subqueries: SELECT 1 vs. SELECT * - Martin Smith](https://stackoverflow.com/a/6140367/2333499) ran tests that show no difference in actual performance though. — SqlZim, Sep 10 '17 at 14:43
thanks. it is (sqlskills.com/blogs/conor/exists-subqueries-select-1-vs-sel‌ect) - good article — user8375976, Sep 10 '17 at 14:55

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

1 Answers1