SQL Server Insert Value that match foreign key-CodePudding

I use SQL Server SGBD and I have the following scenario with 2 tables :

CREATE TABLE D_CLIENT 
(
    ID_CLIENT  varchar(10) NOT NULL,
    NOM_CLIENT varchar(10)  NULL,
    PRIMARY KEY (ID_CLIENT)
)

CREATE TABLE F_FACT 
(
    ANNEE         varchar(10) NOT NULL,
    DOCUMENT      varchar(10) NOT NULL,
    NUM_DOC       varchar(10) NOT NULL,
    NUM_LIGNE_DOC varchar(10) NOT NULL,
    ID_CLIENT     varchar(10) NOT NULL,
    ID_REP        varchar(10) NOT NULL,
    CA            decimal(10,2) NULL,
    PRIMARY KEY (ANNEE, DOCUMENT, NUM_DOC, NUM_LIGNE_DOC),
    CONSTRAINT FK_FactClient 
        FOREIGN KEY (ID_CLIENT) REFERENCES D_CLIENT(ID_CLIENT)
)

INSERT INTO D_CLIENT (ID_CLIENT, NOM_CLIENT)
VALUES ('1', 'A'), ('2', 'B'), ('3', 'C'), ('4', 'D')

INSERT INTO F_FACT (ANNEE, DOCUMENT, NUM_DOC, NUM_LIGNE_DOC, ID_CLIENT, ID_REP, CA)
VALUES ('2022', 'FAC', '1', '1', '1', '1', 100),
       ('2022', 'FAC', '1', '2', '1', '1', 100),
       ('2022', 'FAC', '2', '1', '5', '1', 100)

I have a foreign key on ID_CLIENT for the integrity of data, so if I try to insert a row into F_FACT with an ID_CLIENT which doesn't exist in D_CLIENT, it will fail and it's normal because of foreign key constraint.

So when I execute the INSERT query, I get a error message because the value '5' doesn't exist in the table D_CLIENT but the 2 first row are not inserted either, where the ID_CLIENT does exist in the D_CLIENT table.

My question: is it possible, with a query, to insert only the correct rows (that's means the 2 first rows) and **reject only ** the third row ?

Thanks for your help

CodePudding user response：

Join the source with the lookup table to reject missing values

with src as (
  select * 
  from (
    VALUES 
      ('2022','FAC','1','1','1','1',100),
      ('2022','FAC','1','2','1','1',100),
      ('2022','FAC','2','1','5','1',100)
  ) t(ANNEE, DOCUMENT, NUM_DOC, NUM_LIGNE_DOC, ID_CLIENT, ID_REP, CA)
)
insert into F_FACT(ANNEE, DOCUMENT, NUM_DOC, NUM_LIGNE_DOC, ID_CLIENT, ID_REP, CA)
select src.ANNEE, src.DOCUMENT, src.NUM_DOC, src.NUM_LIGNE_DOC, src.ID_CLIENT, src.ID_REP, src.CA 
from src
join D_CLIENT c on c.ID_CLIENT = src.ID_CLIENT

db<>fiddle

CodePudding user response：

This is something I would use an exists check for:

insert into F_FACT (ANNEE, DOCUMENT, NUM_DOC, NUM_LIGNE_DOC, ID_CLIENT, ID_REP, CA)
select ANNEE, DOCUMENT, NUM_DOC, NUM_LIGNE_DOC, ID_CLIENT, ID_REP, CA from (
  values 
  ('2022','FAC','1','1','1','1',100),
  ('2022','FAC','1','2','1','1',100),
  ('2022','FAC','2','1','5','1',100)
)v(ANNEE, DOCUMENT, NUM_DOC, NUM_LIGNE_DOC, ID_CLIENT, ID_REP, CA)
where exists (select * from D_CLIENT d where d.ID_CLIENT = v.ID_CLIENT)