When you use a stored procedure to process duplicate inserted values (if the inserted id value exists, the inserted id value increases by 1 before being inserted)
Create Table TA (ID int primary key, name varchar (20 ))
Insert Ta
Select 1, 'A'
Union all select 2, 'B'
Union all select 3, 'C'
Union all select 12, 'D'
Union all select 13, 'E'
Stored Procedure:
Create proc test_p @ ID int, @ name varchar (20)
As
Begin
Declare @ J int, @ SQL varchar (1000)
If exists (select 1 from Ta where id = @ ID)
Begin
Select @ J = @ ID, @ SQL =''
While exists (select 1 from Ta where id = @ J)
Begin
Select @ SQL = @ SQL + ',' + rtrim (ID) from Ta where id = @ J
Select @ J = @ J + 1
End
Set @ SQL = stuff (@ SQL, 1, 1 ,'')
Exec ('Update ta Set ID = ID + 1 where ID in ('+ @ SQL + ')')
Insert ta select @ ID, @ name
End
Else
Insert ta select @ ID, @ name
End
Test:
Exec test_p 1, 'F'
Exec test_p 8, 'H'
Exec test_p 12, 'G'
Query:
Select * from Ta
ID name
-------------------------------
1 F
2
3 B
4 C
8 h
12g
13 d
14 E
(The number of affected rows is 8)
-- Drop proc test_p
-- Drop table Ta