SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) 
 Apr 11 2022 16:24:07 
 Copyright (C) 2019 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE DMZ
(
DDM date NOT NULL,
NDM int NOT NULL,
PR int NOT NULL
CONSTRAINT PK_DMZ PRIMARY KEY(NDM)
);
ALTER TABLE DMZ
ADD CONSTRAINT PR CHECK (PR IN (1,2));
INSERT INTO DMZ VALUES('2014.01.04', 20, 1);
INSERT INTO DMZ VALUES('2014.01.04', 21, 1);
INSERT INTO DMZ VALUES('2014.01.04', 22, 2);
INSERT INTO DMZ VALUES('2014.01.04', 23, 1);
INSERT INTO DMZ VALUES('2014.01.04', 24, 1);
INSERT INTO DMZ VALUES('2014.01.04', 25, 2);
INSERT INTO DMZ VALUES('2014.01.04', 26, 2);
7 rows affected

SELECT * FROM DMZ


DDM
NDM
PR


2014-01-04
20
1

2014-01-04
21
1

2014-01-04
22
2

2014-01-04
23
1

2014-01-04
24
1

2014-01-04
25
2

2014-01-04
26
2


INSERT INTO DMZ (a.DDM, a.NDM, a.PR)
SELECT GETDATE() AS DOM,
       a.NDM AS NDM, 
       CASE WHEN a.PR_1_Count > a.PR_2_Count 
        THEN 2
        ELSE 1
       END AS PR
FROM (SELECT
        MAX(NDM) + 1 AS NDM,
        SUM(CASE WHEN PR = 1 THEN 1 ELSE 0 END) AS PR_1_Count, 
        SUM(CASE WHEN PR = 2 THEN 1 ELSE 0 END) AS PR_2_Count
       FROM DMZ) a
1 rows affected

SELECT * FROM DMZ


DDM
NDM
PR


2014-01-04
20
1

2014-01-04
21
1

2014-01-04
22
2

2014-01-04
23
1

2014-01-04
24
1

2014-01-04
25
2

2014-01-04
26
2

2022-10-19
27
2


ALTER TABLE DMZ
ADD NDM_NEW INT IDENTITY(1, 1)
ALTER TABLE DMZ
DROP CONSTRAINT PK_DMZ
ALTER TABLE DMZ DROP COLUMN NDM
SELECT * FROM DMZ


DDM
PR
NDM_NEW


2014-01-04
1
1

2014-01-04
1
2

2014-01-04
2
3

2014-01-04
1
4

2014-01-04
1
5

2014-01-04
2
6

2014-01-04
2
7

2022-10-19
2
8


EXEC sp_rename 'DMZ.NDM_NEW', 'NDM', 'COLUMN'
Caution: Changing any part of an object name could break scripts and stored procedures.


ALTER TABLE DMZ
ADD CONSTRAINT PK_DMZ PRIMARY KEY(NDM)
SELECT * FROM DMZ


DDM
PR
NDM


2014-01-04
1
1

2014-01-04
1
2

2014-01-04
2
3

2014-01-04
1
4

2014-01-04
1
5

2014-01-04
2
6

2014-01-04
2
7

2022-10-19
2
8


INSERT INTO DMZ (a.DDM, a.PR)
SELECT GETDATE() AS DOM,
       CASE WHEN a.PR_1_Count > a.PR_2_Count 
        THEN 2
        ELSE 1
       END AS PR
FROM (SELECT
        SUM(CASE WHEN PR = 1 THEN 1 ELSE 0 END) AS PR_1_Count, 
        SUM(CASE WHEN PR = 2 THEN 1 ELSE 0 END) AS PR_2_Count
       FROM DMZ) a
1 rows affected

SELECT * FROM DMZ


DDM
PR
NDM


2014-01-04
1
1

2014-01-04
1
2

2014-01-04
2
3

2014-01-04
1
4

2014-01-04
1
5

2014-01-04
2
6

2014-01-04
2
7

2022-10-19
2
8

2022-10-19
1
9
DDM	NDM	PR
2014-01-04	20	1
2014-01-04	21	1
2014-01-04	22	2
2014-01-04	23	1
2014-01-04	24	1
2014-01-04	25	2
2014-01-04	26	2