I'm looking for a SQL statement to copy all details from Product A to Product B

I have Table_P that has columns Product, region, salesperson, production date, end date. (There are other columns, but those fields don't matter to me). Product_X has 6 rows: 6 regions, 3 different salespeople and 6 date sets (production date and end date). I want to add a new product, Product_Y to the table, and line by line mirror what Product_X has listed for each column to Product_Y (region, salesperson, production date, end date). I also want to update the existing Product_W, adding all of the same data that I added to Product_Y had without loosing any of the existing unique entries for Product_W.

I tried using this:

INSERT INTO Table_P
    (PRODUCT, REGION, SALESPERSON, PROD_DATE, END_DATE)
  SELECT 'PRODUCT_W', REGION, SALESPERSON, SYSDATE(), TO_DATE ('31-12-9999', 'DD-MM-YYYY')
  FROM Table_P
  WHERE PRODUCT = 'PRODUCT_X'

This works, but it also adds records from PRODUCT_X that already match what's in PRODUCT_W, resulting in duplicates. I need to add a WHERE NOT... or something to check for existing matches and skip those. This sql statement needs to run in Snowflake - if that helps.

Solution

-- Insert data from Product_X to Product_W, avoiding duplicates
INSERT INTO Table_P (PRODUCT, REGION, SALESPERSON, PROD_DATE, END_DATE)
SELECT 'PRODUCT_W', REGION, SALESPERSON, PROD_DATE, END_DATE
FROM Table_P AS px
WHERE PRODUCT = 'PRODUCT_X'
  AND NOT EXISTS (
      SELECT 1
      FROM Table_P AS pw
      WHERE pw.PRODUCT = 'PRODUCT_W'
        AND pw.REGION = px.REGION
        AND pw.SALESPERSON = px.SALESPERSON
        AND pw.PROD_DATE = px.PROD_DATE
        AND pw.END_DATE = px.END_DATE
  );

Inserting data from Product_X to Product_W, avoiding duplicates:

This query checks if a similar row already exists in Product_W before inserting it.

The NOT EXISTS clause ensures that only rows that do not already exist in Product_W are inserted.

It compares the REGION, SALESPERSON, PROD_DATE, and END_DATE columns to ensure no duplicates.

I hope this helps