Add Row in between two rows in SQL Server

Hi suhaas121,

Refer below query.

SQL

CREATE TABLE #tblModule
(
	iModuleId INT,
	vcModuleTitle NVARCHAR(250),
	iChapterId INT
)
INSERT INTO #tblModule VALUES(1,'Sample',1)
INSERT INTO #tblModule VALUES(2,'Sample1',1)
INSERT INTO #tblModule VALUES(3,'Sample3',1)

DECLARE @iModuleId INT, @imodtxt NVARCHAR(250),@iChapterId INT
SET @IModuleId = 2
SET @imodtxt = 'Sample4'
SET @iChapterId = 2

UPDATE #tblModule set iModuleId=@iModuleId+2 WHERE iModuleId > @iModuleId
INSERT INTO #tblModule (iModuleId,vcModuleTitle,iChapterId) VALUES (@IModuleId+1,@imodtxt,@iChapterId)

SELECT * FROM #tblModule ORDER BY iModuleId

DROP TABLE #tblModule

Output

iModuleId vcModuleTitle iChapterId

    1              Sample                 1

    2              Sample1               1

    3              Sample4               2

    4              Sample3               1