java - How to update fields in a table and insert it into new table with out updating the old table

Question

I am working on java with JDBC connections and trying to perform DDL commands. Here i had a doubt about one particular flow, can that be possible? if yes, can you explain me how and what to do with example.

I am trying to select data from item table containing item_id, sale_price, description, barcode columns and want to update barcode data for item_id = 9 and insert into item_duplicate table. With out updating the item table. But item_dupliacte table should contain the updated value for barcode.

my item_duplicate table

item table

MERGE item_duplicate AS D  
USING item AS I
ON  (D.item_id = I.item_id )
WHEN MATCHED 
THEN UPDATE set D.part_no='new part'                     
WHEN NOT MATCHED BY D
THEN 
INSERT (item_id,part_no,sale_price,description,barcode) 
 SELECT i.ITEM_ID,i.PART_NO,i.SALE_PRICE,i.DESCRIPTION,b.BARCODE
 FROM item i 
JOIN item_barcode b
 ON b.ITEM_ID = i.ITEM_ID 
WHERE i.ITEM_ID = ? 
WHEN NOT MATCHED BY I
THEN DELETE;

question from:https://stackoverflow.com/questions/65940207/how-to-update-fields-in-a-table-and-insert-it-into-new-table-with-out-updating-t

Answer 1

A simple insert into select from.

CREATE TABLE dbo.TEST
(
      item_id     INT NOT NULL
    , barcode     VARCHAR (20) NULL
    , sale_price  DECIMAL (14, 2) NULL
    , description VARCHAR (100) NULL
    , PRIMARY KEY (item_id)
)

CREATE TABLE dbo.TEST_COPY
(
      item_id     INT NOT NULL
    , barcode     VARCHAR (20) NULL
    , sale_price  DECIMAL (14, 2) NULL
    , description VARCHAR (100) NULL
    , PRIMARY KEY (item_id)
)


INSERT INTO TEST_COPY (item_id, barcode, sale_price, description)  SELECT item_id, '9999' as barcode, sale_price, description FROM TEST WHERE item_id = 9