How to duplicate rows based on conditions-CodePudding

I have the following tables:

create table Contacts (key1 int, Accn varchar (20))
INSERT INTO Contacts 
select 1234,'ABCD' UNION
select 1234,'EFG' UNION
select 1234,'GAB' UNION
select 1234,'PBC' UNION
select 89023,'MONQ'  
   

CREATE TABLE SCANDOCS (Accn varchar (20), FileDesc varchar (50),BReg  varchar (50))
select 'ABCD','100_MP','813' union
select 'ABCD','200_KA','843' union
select 'EFG','100_MP','3209' union
select 'GAB','800_JLZG','2357' union
select 'MONQ','700_NMO','94266'

I need to find Key1 and check that its accn in Scandocs in the column filedesc has either the values '100_MP' or '200_KA'. In the example it exists for Key1 =1234 and ACCN=ABCD,EFG. Once I find it for a specific key I need to insert a row into Scandocs for all the other accn under that key that don't have these filedesc.

Also, if an accn under a key has one of these values but with a different BReg then another acc under the same key with same fildesc I need to also insert that row into the scandocs with the accn that doesn't have that same filedesc and Breg.

The outcome should be

accn      | filedesc  | Breg
ABCD      | 100_MP    |813
ABCD      | 200_KA    |843
ABCD      | 100_MP    |3209
EFG       | 100_MP    |813
EFG       | 100_MP    |3209
EFG       | 200_KA    |843
GAB       |800_JLZG   |2357
GAB       | 100_MP    |813
GAB       | 200_KA    |843
GAB       | 100_MP    |3209
MONQ      |700_NMO    |94266

MONQ acc is under key1=89023 and no acc under that key1 has filedesc with values 100_mp or 200_KA so we don't need to copy rows with 100 or 200 since they don't exist for that key1.

Thanks

CodePudding user response：

If you generate all that should be there.
Then you can compare that with what's already there.

The solution here inserts all from CTE_ALL_ACC_FD that doesn't match with what's already in SCANDOCS.

WITH CTE_ACCOUNTS AS (
  SELECT c.accn, c.key1
  FROM Contacts c
  INNER JOIN SCANDOCS d 
    ON d.accn = c.accn
  WHERE c.key1 = 1234
  GROUP BY c.accn, c.key1
)
, CTE_FILEDESCS AS
(
  SELECT DISTINCT filedesc, breg
  FROM SCANDOCS
  WHERE accn IN (select accn from CTE_ACCOUNTS)
    AND filedesc IN ('100_MP','200_KA')
)
, CTE_ALL_ACC_FD AS (
  SELECT DISTINCT accn, filedesc, breg
  FROM CTE_ACCOUNTS a
  CROSS JOIN CTE_FILEDESCS fd
)
INSERT INTO SCANDOCS (
 accn, 
 filedesc, 
 breg
)
SELECT 
 afd.accn, 
 afd.filedesc, 
 afd.breg
FROM CTE_ALL_ACC_FD afd
LEFT JOIN SCANDOCS sdoc 
  ON sdoc.accn = afd.accn
 AND sdoc.filedesc = afd.filedesc
 AND sdoc.breg = afd.breg
WHERE sdoc.filedesc IS NULL;

6 rows affected

SELECT *
FROM SCANDOCS
ORDER BY accn, filedesc

accn | filedesc | breg 
:--- | :------- | :----
ABCD | 100_MP   | 3209 
ABCD | 100_MP   | 813  
ABCD | 200_KA   | 843  
EFG  | 100_MP   | 3209 
EFG  | 100_MP   | 813  
EFG  | 200_KA   | 843  
GAB  | 100_MP   | 3209 
GAB  | 100_MP   | 813  
GAB  | 200_KA   | 843  
GAB  | 800_JLZG | 2357 
MONQ | 700_NMO  | 94266

db<>fiddle here