CODEWITHSUNDEEP
sqlpostgresql
Sritam Jagadev
Sritam Jagadev

Reputation: 975

Insert multiple in a single query on duplicate insert that duplicate copy to copytable in Postgres

I am inserting multiple records in single query to a table. But, here I am skipping duplicate records. I need to insert those duplicate copies to another table (copytable) with same structure instead of skipping those records. And both the activity needs to be done in a single statement. i.e insert into first table and duplicates records into second table

INSERT INTO manager.vin_manufacturer
(SELECT * FROM( VALUES
('935',' Citroën Brazil','Citroën'),
('ABC', 'Toyota', 'Toyota'),
('ZOM',' OM','OM')
) as tmp (vin_manufacturer_id, manufacturer_desc, make_desc)
WHERE NOT EXISTS (
SELECT 1 FROM manager.vin_manufacturer m where m.vin_manufacturer_id = 
tmp.vin_manufacturer_id)
)

Upvotes: 0

Views: 37

Answers (1)

user330315
user330315

Reputation:

You can do that in a single statement, but you have to repeat the where condition that detects the existing rows (just with a negated condition):

with tmp (vin_manufacturer_id, manufacturer_desc, make_desc) as (
  VALUES
    ('935',' Citroën Brazil','Citroën'),
    ('ABC', 'Toyota', 'Toyota'),
    ('ZOM',' OM','OM')
), inserted as (
  -- insert but skip duplicates
  INSERT INTO manager.vin_manufacturer (vin_manufacturer_id, manufacturer_desc, make_desc)
  SELECT vin_manufacturer_id, manufacturer_desc, make_desc
  FROM tmp
  WHERE NOT EXISTS (SELECT 1 
                    FROM manager.vin_manufacturer m 
                    where m.vin_manufacturer_id = tmp.vin_manufacturer_id)
  returning * -- return all inserted rows
)
-- insert the duplicates into a different table
insert into duplicates_table (vin_manufacturer_id, manufacturer_desc, make_desc)
select vin_manufacturer_id, manufacturer_desc, make_desc
from tmp
WHERE NOT EXISTS (select * 
                  from inserted i 
                  where i.vin_manufacturer_id = tmp.vin_manufacturer_id)

Upvotes: 1

Related Questions