I have this below query. I like to insert only if the row is not present in TABLE1. How can I do that?
INSERT INTO TABLE1 (VEH_YEAR, VEH_MAKE, ACV_VOLUME)
SELECT VEH_YEAR, VEH_MAKE,
(SELECT COUNT(*)
FROM ACV_VEHICLE_DETAILS
WHERE YEAR = table2 .veh_year
AND MAKE = table2 .veh_make
) AS ACV_VOLUME
FROM TABLE2 table2 WHERE VEH_YEAR IS NOT NULL AND VEH_MAKE IS NOT NULL;
Use not exists:
INSERT INTO TABLE1 (VEH_YEAR, VEH_MAKE, ACV_VOLUME)
SELECT VEH_YEAR, VEH_MAKE,
(SELECT COUNT(*)
FROM ACV_VEHICLE_DETAILS vd
WHERE vd.YEAR = t2.veh_year AND vd.MAKE = t2.veh_make
) AS ACV_VOLUME
FROM TABLE2 t2
WHERE VEH_YEAR IS NOT NULL AND VEH_MAKE IS NOT NULL AND
NOT EXISTS (SELECT 1
FROM table1 t1
WHERE t1.veh_year = t2.veh_year and t1.veh_make = t2.veh_make
);
veh_make has extra characters in it.You can use MERGE with WHEN NOT MATCHED THEN INSERT:
merge into Table1 t
using (
SELECT VEH_YEAR, VEH_MAKE,
(SELECT COUNT(*)
FROM ACV_VEHICLE_DETAILS
WHERE YEAR = table2 .veh_year
AND MAKE = table2 .veh_make
) AS ACV_VOLUME
FROM TABLE2 table2 WHERE VEH_YEAR IS NOT NULL AND VEH_MAKE IS NOT NULL
) d
on (t.veh_year = d.veh_year and t.veh_make = d.veh_make)
when not matched then insert (veh_year, veh_make, acv_volume)
values (d.veh_year, d.veh_make, d.acv_volume);
Don't forget to commit :)
You can use NOT EXISTS like this:
INSERT INTO TABLE1 ( VEH_YEAR, VEH_MAKE, ACV_VOLUME)
SELECT VEH_YEAR
, VEH_MAKE
, ( SELECT COUNT(*)
FROM ACV_VEHICLE_DETAILS
WHERE YEAR = t2.veh_year
AND MAKE = t2.veh_make
) AS ACV_VOLUME
FROM TABLE2 t2
WHERE VEH_YEAR IS NOT NULL
AND VEH_MAKE IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM Table1 t1
WHERE t1.VEH_YEAR = t2.VEH_YEAR
AND t1.VEH_MAKE = t2.VEH_MAKE
);
This assumes you want to exclude only when both the VEH_MAKE and VEH_YEAR are already present, if you want to exclude from INSERT all records based on VEH_MAKE alone you just alter the correlating WHERE criteria, excluding t1.VEH_YEAR = t2.VEH_YEAR
Classic LEFT JOIN:
INSERT INTO TABLE1 (VEH_YEAR, VEH_MAKE, ACV_VOLUME)
SELECT VEH_YEAR, VEH_MAKE, ACV_VOLUME
FROM(SELECT VEH_YEAR, VEH_MAKE,
(SELECT COUNT(*)
FROM ACV_VEHICLE_DETAILS
WHERE YEAR = table2.veh_year
AND MAKE = table2.veh_make) AS ACV_VOLUME
FROM TABLE2 table2 WHERE VEH_YEAR IS NOT NULL AND VEH_MAKE IS NOT NULL
)t
LEFT JOIN TABLE1 t1 on t.VEH_YEAR = t1.VEH_YEAR AND t.VEH_MAKE = t1.VEH_MAKE AND t.ACV_VOLUME = t1.ACV_VOLUME
WHERE t1.VEH_YEAR IS NULL
