How to update existing columns during insert

Question

How to update existing columns during insert

Mizuno8482 20

Greetings!

I have a SQL job that runs the following SQL script every Monday.

WITH Result1

AS

(

select

p.Customer,

p.ProductNumber,

p.CurrentCost

from price p

)

--Main Select

insert into Price_Archive

(

[DateTime], [Customer], [ProductNumber], [CurrentCost], [PreviousCost], [ChangeDate]

)

select distinct

CURRENT_TIMESTAMP as [DateTime],

r1.customer,

r1.ProductNumber,

r1.CurrentCost,

'' as PreviousCost,

'' as ChangeDate

from result1 r1

My question is, when the data is inserted on Monday's can it update the most recent prior inserted record with current data that's being inserted, if it finds a matching customer and product number that has a different CurrentCost from that previous record?

Here is example data from the last three mondays in July

User's image

Notice that when the data was inserted on 7/21 it found a matching customer and product number with a different current cost, so it needs to add the previous records (7/14/2025) CurrentCost into the PreviousCost column as well as the previous records DateTime into the ChangeDate column.

Here is an example of what it would look like over time.

User's image

Notice on 6/30 when the records inserted, it found a matching customer and product number with a different cost so it inserted the prior records current cost and DateTime into the current records PreviousCOst and ChangeDate columns.

Quick note...if it doesnt find a different CurrentCost for a matching customer and product number it should just insert the data as is. Hopefully I explained this well and I appreciate any help!!

Accepted answer

0 additional answers

Your answer

Answer 1

To that end you would use the MERGE statement. Had you posted your table definitions and sample data as CREATE TABLE + INSERT, I would have shown how to do it with your data. But since I'm to lazy to figure that out from your post, here is just a generic example that you can work from:

CREATE TABLE data (key1  int NOT NULL,
                   key2  int NOT NULL,
                   somedata varchar(23) NULL,
                   somemoredata nvarchar(87) NULL,
                   CONSTRAINT pk_data PRIMARY KEY(key1, key2)
)
go
-- First set of data.
INSERT data (key1, key2, somedata, somemoredata)
   VALUES(1, 1, 'This', 'That'),
         (1, 2, 'Also this', 'Also that'),
         (1, 3, 'This', 'But not that'),
         (2, 1, NULL, 'Maybe')
go
SELECT * FROM data
go
-- A week later, new data comes in. Put it in a temp table
-- for the sake of the demo.
CREATE TABLE #newdata(key1  int NOT NULL,
                      key2  int NOT NULL,
                      somedata varchar(23) NULL,
                      somemoredata nvarchar(87) NULL,
                      PRIMARY KEY(key1, key2)
)
INSERT #newdata(key1, key2, somedata, somemoredata)
   VALUES(1, 1, 'New', 'Also new'),
         (1, 3, 'Not this', 'But that'),
         (5, 5, 'Take', 'Five')
go
MERGE data d
USING #newdata n ON d.key1 = n.key1
                AND d.key2 = n.key2
WHEN NOT MATCHED BY TARGET THEN
   INSERT (key1, key2, somedata, somemoredata)
     VALUES(n.key1, n.key2, n.somedata, n.somemoredata)
WHEN MATCHED THEN
   UPDATE
   SET    somedata = n.somedata,
          somemoredata = n.somemoredata
;
go
SELECT * FROM data
go
-- Cleanup
DROP TABLE #newdata
DROP TABLE data

Mizuno8482 20 Reputation points

2025-07-23T13:46:50.8833333+00:00

Hi Erland,

Thank you for the response. A merge wont work because it will only update existing records and not insert my new records. The new records need to be inserted no matter what and will always have a matching customer and product number which is the primary keys and therefore because they match it will always update vs insert.

The challenge I am having is it needs to insert the data always AND when it finds a matching customer and product number with a different currentCost and ONLY if it finds a different currentcost would it UPDATE the previous record. Does that make sense?
Mizuno8482 20 Reputation points

2025-07-23T14:54:22.56+00:00

Hi Erland,

Never mind this does work, thank you! I thought more about it, and I am going to let week 2 insert as normal and AFTER it inserts, I will have the same dataset go into a temp table to do the MERGE statement. Thank you for pointing me in the right direction!

Share via

How to update existing columns during insert

0 additional answers

Your answer