question

The problem is really simple. You're getting more than one value returned from the match between the two tables and you're only providing a single value for the @maxs variable, @maxs+1, which will always be the same. You should look at modifying the t1 table to use an identity value for the primary key so that you can insert multiple rows. You can validate this by running this query: SELECT * FROM dbo.t1 AS t JOIN dbo.t2 AS s ON t.id = s.id AND t.ids = s.ids WHERE t.sal != s.sal; From your sample data, that returns two rows. On the second row, this query is attempting to insert the same PK value and it can't.

I would also recommend a design level change by start using IDENTITY column. Although that would reuire creating a new table, copying all the data into the new table and then renaming of the tables, it will make sure that you would not be getting primary key error if this procedure is ran simultaneouly. Reason being, the MAX Id could be read the same by multiple users unless you are using most restricted Isolation level OR placing an exclusive LOCK to be held till the transaction commits (which is not seen in your code). But if the Design level change is not feasible/permitted, then I cannot see any other viable option but to revert to orthodox UPDATE,INSERT in two steps. Also make sure that no one else reads the same MAX Id and be able to INSERT/UPDATE the table during the execution of this code. Application Lock feature have also proved to be handy in such cases. There are workarounds like using a Derived table but that would not give you the IDs in sequence. For e.g. something like declare @maxs int select @maxs=max(lineId) from t1 --DECLARE @T TABLE(id varchar(10),ids int); MERGE t1 AS T USING (SELECT s.id, s.ids, s.sal, @maxs+ROW_NUMBER() OVER(ORDER BY (SELECT @maxs))NewLineId FROM t2 S) AS S ON t.id = s.id and t.ids = s.ids --WHEN NOT MATCHED BY TARGET -- THEN INSERT VALUES ( s.id, s.ids, s.sal,getdate()-1) WHEN MATCHED AND (t.sal != s.sal) THEN --Row exists and data is different UPDATE SET t.sal = s.sal, t.updatedDate=getdate() WHEN NOT MATCHED BY TARGET THEN INSERT(id,ids,sal,updateddate,lineid) VALUES( s.id, s.ids, s.sal,getdate()-1, NewLineId); Use of a temporary table would be another workaround but could prove to be a bad solution. So to me, if @Grant Fritchey 's excellent advice is not feasible, then break down the solution into two steps i.e. UPDATE and INSERT.