MERGE with Sequential Numbering

I have a table with several fields which includes a column called apporder which is just an integer. The value is used for the order in which it is displayed on the front end. So it would be like 1, 2, 3, 4, etc.

I am trying to merge new values into that table, and each new insert should be 1 higher than the max.

This is what I am trying, but it is always inserting the max value + 1 from the table, not taking into account any new rows inserted. How can I accomplish this.

MERGE INTO table2 USING
	(SELECT field1,field2,apporder FROM table1) a ON a.field1 = table2.field1
WHEN NOT MATCHED TEHN
	INSERT
		(
			field1,
			field2,
			apporder
		)
	VALUES
		(
			'a',
			'b',
			(SELECT ISNULL(MAX(apporder),0) + 1 FROM table2)
		)

It looks like you want some kind of numbering column grouped by field1. But MERGE does not do this, it simply identifies a single matching row if any, and updates or inserts it. Instead just use a normal INSERT.

To do this without running into concurrency problems, you are going to need the UPDLOCK and SERIALIZABLE hints on the subquery. You are going to end up locking each complete group, and obviously you will get holes if anyone deletes a row.

You also need to take into account multiple rows being inserted at once. You can use ROW_NUMBER for this.

INSERT table2
  (field1, field2, apporder)
SELECT
  t1.field1,
  t1.field2,
  (
   SELECT ISNULL(MAX(t2.apporder), 0)
   FROM table2 t2 WITH (UPDLOCK, SERIALIZABLE)
   WHERE t2.field1 = t1.field1
  ) + ROW_NUMBER() OVER (PARTITION BY t1.field1 ORDER BY t1.field2)
FROM table1 t1;

If possible, you should just forgo this column completely, and instead calculate it using ROW_NUMBER when you query the table

SELECT
  t2.field1,
  t2.field2,
  apporder = ROW_NUMBER() OVER (PARTITION BY t1.field1 ORDER BY t1.field2)
FROM table2 t2
WHERE t2.field1 = 'something';

Based on what you provided, I am assuming that you first need to get the maximum apporder value from table2 and increment it by 1 then include that value in the INSERT statement when inserting the new rows and make sure that they will always be inserted with a value higher than the current maximum value, even if new rows have been inserted concurrently.

CREATE TABLE YourTable (
  field1 VARCHAR(255),
  field2 VARCHAR(255),
  apporder INT
);

CREATE TABLE YourTable2 (
  field1 VARCHAR(255),
  field2 VARCHAR(255),
  apporder INT
);

INSERT INTO YourTable (field1, field2, apporder)
VALUES
  ('Data 1', 'Value 1', 1),
  ('Data 2', 'Value 2', 2),
  ('Data 3', 'Value 3', 3),
  ('Data 4', 'Value 4', 4);

4 rows affected

INSERT INTO YourTable2 (field1, field2, apporder)
VALUES
  ('Data 1', 'Value 1', 10),
  ('Data 2', 'Value 2', 22),
  ('Data 6', 'Value 3', 35),
  ('Data 5', 'Value 4', 42);

4 rows affected

MERGE INTO YourTable2 AS tgt
USING (
  SELECT field1, field2, MAX(apporder) AS maxAppOrder
  FROM YourTable
  GROUP BY field1, field2
) AS src ON tgt.field1 = src.field1 AND tgt.field2 = src.field2
WHEN NOT MATCHED THEN
  INSERT (field1, field2, apporder)
  VALUES (src.field1, src.field2, src.maxAppOrder + 1);

2 rows affected

SELECT * FROM YourTable
SELECT * FROM YourTable2

fiddle