Keep track of item's versions after new insert-CodePudding

I'm currently working on creating a Log Table that will have all the data from another table and will also have recorded, as Versions, changes in the prices of items in the main table.

I would like to know how it is possible to save the versions, that is, increment the value 1 at each insertion of the same item in the Log table.

The Log table is loaded via a Merge of data coming from the User API, on a python script using PYODBC:

MERGE LogTable as t
USING (Values(?,?,?,?,?)) AS s(ID, ItemPrice, ItemName)
ON t.ID = s.ID AND t.ItemPrice= s.ItemPrice 
WHEN NOT MATCHED BY TARGET 
THEN INSERT (ID, ItemPrice, ItemName, Date)
VALUES (s.ID, s.ItemPrice, s.ItemName, GETDATE())

Table example:

Id	ItemPrice	ItemName	Version	Date
1	50	Foo	1	Today
2	30	bar	1	Today

And after inserting the Item with ID = 1 again with a different price, the table should look like this:

Id	ItemPrice	ItemName	Version	Date
1	50	Foo	1	Today
2	30	bar	1	Today
1	45	Foo	2	Today

Saw some similar questions mentioning using triggers but in these other cases it was not a Merge used to insert the data into the Log table.

CodePudding user response：

May the following helps you, modify your insert statement as this:

Insert Into tbl_name
Values (1, 45, 'Foo', 
COALESCE((Select MAX(D.Version) From tbl_name D Where D.Id = 1), 0)   1, GETDATE())

See a demo from db<>fiddle.