NOT IN SELECT TO APPEND

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

NOT IN SELECT TO APPEND

Post by sal21 »

I need to append in PREFISSI only if not in select PR, CAP, PREF, PROVINCIA:

INSERT INTO PREFISSI (PR, CAP, PREF, PROVINCIA)
SELECT PREFISSI_OK.PR, PREFISSI_OK.CAP, PREFISSI_OK.PRFX, PREFISSI_OK.PROVINCIA
FROM PREFISSI_OK

in effect i need to append only the value in PREFISSI_OK, not present...

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: NOT IN SELECT TO APPEND

Post by HansV »

INSERT INTO PREFISSI (PR, CAP, PREF, PROVINCIA)
SELECT PREFISSI_OK.PR, PREFISSI_OK.CAP, PREFISSI_OK.PRFX, PREFISSI_OK.PROVINCIA
FROM PREFISSI_OK
WHERE NOT EXISTS
(SELECT PREFISSI.PR, PREFISSI.CAP, PREFISSI.PREF, PREFISSI.PROVINCIA
FROM PREFISSI
WHERE PREFISSI.PR=PREFISSI_OK.PR AND PREFISSI.CAP=PREFISSI_OK.CAP AND PREFISSI.PREF=PREFISSI_OK.PRFX AND PREFISSI.PROVINCIA=PREFISSI_OK.PROVINCIA)
Best wishes,
Hans