Add Row in between two rows in SQL Server

Last Reply 6 months ago By arunkurmi

Posted 6 months ago

Hi i want to add a row between 2 columns in sql server.

my requirment is i want to add a row between 2 & 3.

iD should change like ID 3 should become 4 and i have insert a new row for ID 3. 

https://imgur.com/qtE7Qyd

i have written a procedure.

ALTER PROCEDURE [dbo].[sp_insertnew]
    @iModuleId int,
    @imodtxt nvarchar(250)
    @iChapterId int
as
begin
    update tblModule set iModuleId=iModuleId+1000 where iModuleId>@iModuleId
    update tblModule set iModuleId=iModuleId-999 where iModuleId>@iModuleId
    insert into tblModule (iModuleId,vcModuleTitle,iChapterId) values (@iModuleId+1,@imodtxt,@iChapterId)
end
You are viewing reply posted by: arunkurmi 6 months ago.
Posted 6 months ago Modified on 6 months ago

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