SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tablename ([ID] int, [SQ] int, [Code] int);
    
INSERT INTO tablename ([ID], [SQ], [Code]) VALUES
    (123456, 1, 123), (654321, 1, 369), (123456, 2, 234);
3 rows affected

SELECT * FROM tablename;


ID
SQ
Code


123456
1
123

654321
1
369

123456
2
234


WITH cte AS (SELECT * FROM (VALUES (123456, 123))t (ID, Code))
INSERT INTO tablename (ID, SQ, Code)
SELECT c.ID,
       COALESCE(MAX(t.SQ), 0) + 1,
       c.Code
FROM cte c LEFT JOIN tablename t
ON t.ID = c.ID 
GROUP BY c.ID, c.Code
HAVING MAX(CASE WHEN c.Code = t.Code THEN 1 END) IS NULL;
Warning: Null value is eliminated by an aggregate or other SET operation.


SELECT * FROM tablename;


ID
SQ
Code


123456
1
123

654321
1
369

123456
2
234


WITH cte AS (SELECT * FROM (VALUES (654321, 123))t (ID, Code))
INSERT INTO tablename (ID, SQ, Code)
SELECT c.ID,
       COALESCE(MAX(t.SQ), 0) + 1,
       c.Code
FROM cte c LEFT JOIN tablename t
ON t.ID = c.ID 
GROUP BY c.ID, c.Code
HAVING MAX(CASE WHEN c.Code = t.Code THEN 1 END) IS NULL;
Warning: Null value is eliminated by an aggregate or other SET operation.
1 rows affected

SELECT * FROM tablename;


ID
SQ
Code


123456
1
123

654321
1
369

123456
2
234

654321
2
123


WITH cte AS (SELECT * FROM (VALUES (999999, 123))t (ID, Code))
INSERT INTO tablename (ID, SQ, Code)
SELECT c.ID,
       COALESCE(MAX(t.SQ), 0) + 1,
       c.Code
FROM cte c LEFT JOIN tablename t
ON t.ID = c.ID 
GROUP BY c.ID, c.Code
HAVING MAX(CASE WHEN c.Code = t.Code THEN 1 END) IS NULL;
Warning: Null value is eliminated by an aggregate or other SET operation.
1 rows affected

SELECT * FROM tablename;


ID
SQ
Code


123456
1
123

654321
1
369

123456
2
234

654321
2
123

999999
1
123