CODEWITHSUNDEEP
sqlsql-serverinsert
user8375976
user8375976

Reputation: 11

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

Upvotes: 1

Views: 323

Answers (1)

SqlZim
SqlZim

Reputation: 38033

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:

Upvotes: 1

Related Questions