-- Requirement Description:/* ID Col ---------- ab00001 aab00002 B -- when data is inserted, the ID is automatically changed to ab00003 */-- 1. evaluate the maximum value method (not applicable in high concurrency, but an introduction) -- test data if object_id ('[macotb]') is not null drop table [macotb] Create Table [macotb] (ID varchar (7), Col varchar (1) insert into [macotb] Select 'ab00001 ', 'A' Union allselect 'ab00002 ',' B 'declare @ Max varchar (7) select @ max = 'AB' + right ('000000' + ltrim (max (replace (ID, 'AB', '') + 1), 5) from [macotb] Insert Into [macotb] Select @ Max, 'C' select * from [macotb]/* ID Col ------- ---- ab00001 aab00002 bab00003 C */-- 2. use @ identity to step-by-step processing if object_id ('[macotb]') is not null drop table [macotb] Create Table [macotb] ([No] int identity, id varchar (7), Col varchar (1) insert into [macotb] Select 'ab000000001', 'A' Union allselect 'ab00002 ', 'B' insert into [macotb] (COL) Select 'C' update [macotb] Set ID = 'AB' + right ('66661' + ltri M ([No]), 5) where [No] = @ identityselect ID, Col from [macotb]/* ID Col ------- ---- ab00001 aab00002 bab00003 C */-- 3. directly add the operation column if object_id ('[macotb]') is not null drop table [macotb] Create Table [macotb] ([No] int identity, ID as ('AB' + right ('000000' + ltrim ([No]), 5), Col varchar (1) insert into [macotb] (COL) select 'A' Union all select 'B' select ID, Col from [macotb]/* ID Col ------------ ---- ab00001 aab00002 B */Insert into [macotb] (COL) Select 'C' Union all select 'D' select ID, col from [macotb]/* ID Col ------------ ---- ab00001 aab00002 bab00003 cab00004 D */-- the third method is recommended for leaf!