Loop Through Table in SQL Server WITH and WITHOUT CURSOR

Aim:Copy Raw table data to two actual tables with our logical conditions like age calculations from Dateofbirth.

We can do this with using CURSOR or without using CURSOR

Create Raw Table as 

CREATE TABLE MY_DUMP

(

ID BIGINT NOT NULL IDENTITY(1,1),

P_NO VARCHAR(50),

NAME VARCHAR(50),

DOB DATE,

PRIMARY KEY(ID)

);

SELECT * FROM MY_DUMP;

Create Actual Table One and Two as

CREATE TABLE MY_MAIN

(

ID BIGINT NOT NULL IDENTITY(1,1),

P_NO VARCHAR(50),

NAME VARCHAR(50),

AGE INT,

MY_ID VARCHAR(50),

MEM_COUNT INT,

PRIMARY KEY(ID)

);

SELECT * FROM MY_MAIN;

CREATE TABLE MY_SUB

(

ID BIGINT NOT NULL IDENTITY(1,1),

MY_MAIN_ID BIGINT NOT NULL,

NAME VARCHAR(50),

AGE INT,

MY_ID VARCHAR(50),

SL_NO TINYINT,

PRIMARY KEY(ID),

FOREIGN KEY(MY_MAIN_ID) REFERENCES MY_MAIN(ID)

);

SELECT * FROM MY_SUB;

Insert some raw data into raw table.

DECLARE @N INT;

SET @N=1;

WHILE(@N <10)

BEGIN

INSERT INTO MY_DUMP(P_NO,NAME,DOB)

VALUES('POL'+CAST(@N AS VARCHAR(10)),'NAME'+CAST(@N AS VARCHAR(10)),DATEADD(year, -(@N*8), GETDATE()));

SET @N=@N+1;

END;

UPDATE MY_DUMP SET P_NO='POL1' WHERE ID IN(1,2,3);

UPDATE MY_DUMP SET P_NO='POL2' WHERE ID IN(4,5,6);

UPDATE MY_DUMP SET P_NO='POL3' WHERE ID IN(7);

UPDATE MY_DUMP SET P_NO='POL4' WHERE ID IN(8,9);

/*WITH CURSOR*/

WITH CURSOR:Retrieving row by row from the current position in the result set.

DECLARE @P_NO VARCHAR(50)

DECLARE MY_CUR CURSOR LOCAL FOR SELECT P_NO FROM MY_DUMP GROUP BY P_NO;

OPEN MY_CUR

FETCH NEXT FROM MY_CUR INTO @P_NO

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @ID VARCHAR(50);

DECLARE MY_CUR_SUB CURSOR LOCAL FOR SELECT ID FROM MY_DUMP WHERE P_NO=@P_NO;

OPEN MY_CUR_SUB

FETCH NEXT FROM MY_CUR_SUB INTO @ID

--INSERT TO MAIN

DECLARE @NAME VARCHAR(50);

DECLARE @DOB DATE;

DECLARE @MY_ID INT;

DECLARE @MEM_COUNT INT;

DECLARE @SL_NO INT;

SET @SL_NO=1;

SELECT @NAME=NAME,@DOB=DOB FROM MY_DUMP WHERE ID=@ID;

SELECT @MY_ID=ISNULL(MAX(ID),0)+1 FROM MY_MAIN;

SELECT @MEM_COUNT=COUNT(ID) FROM MY_DUMP WHERE P_NO=@P_NO

INSERT INTO MY_MAIN(P_NO,NAME,AGE,MY_ID,MEM_COUNT)

SELECT @P_NO,@NAME,DATEDIFF(year,@DOB,GETDATE()),'ABCD-'+CAST(@MY_ID AS VARCHAR(10)),@MEM_COUNT;

WHILE @@FETCH_STATUS = 0

BEGIN

--INSERT TO SUB

SELECT @NAME=NAME,@DOB=DOB FROM MY_DUMP WHERE ID=@ID;

INSERT INTO MY_SUB(MY_MAIN_ID,NAME,AGE,MY_ID,SL_NO)

SELECT @MY_ID,@NAME,DATEDIFF(year,@DOB,GETDATE()),'ABCD-'+CAST(@MY_ID AS VARCHAR(10))+'-'+CAST(@SL_NO AS VARCHAR(10)),@SL_NO;

SET @SL_NO=@SL_NO+1;

FETCH NEXT FROM MY_CUR_SUB INTO @ID

END

CLOSE MY_CUR_SUB

DEALLOCATE MY_CUR_SUB

FETCH NEXT FROM MY_CUR INTO @P_NO

END

CLOSE MY_CUR

DEALLOCATE MY_CUR

/*WITH OUT CURSOR 2005/2008*/

WITH OUT CURSOR:Retrieving row by row using ROW_NUMBER

DECLARE @P_NO VARCHAR(50);

DECLARE @MY_CUR TABLE(P_NO VARCHAR(50));

INSERT INTO @MY_CUR(P_NO)

SELECT P_NO FROM MY_DUMP GROUP BY P_NO;

DECLARE @MY_CUR_ROW_COUNT INT;

SET @MY_CUR_ROW_COUNT=1;

WHILE (@MY_CUR_ROW_COUNT<=(SELECT COUNT(P_NO) FROM @MY_CUR))

BEGIN

DECLARE @MY_CUR_SUB TABLE(ID INT);

SELECT @P_NO=P_NO FROM ( SELECT

ROW_NUMBER() OVER (ORDER BY P_NO ASC) AS ROWNUMBER,

P_NO

FROM @MY_CUR

) AS TEMP_TAB

WHERE ROWNUMBER = @MY_CUR_ROW_COUNT;

INSERT INTO @MY_CUR_SUB(ID)

SELECT ID FROM MY_DUMP WHERE P_NO =@P_NO;

DECLARE @MY_CUR_SUB_ROW_COUNT INT;

SET @MY_CUR_SUB_ROW_COUNT=1;

--INSERT TO MAIN

DECLARE @NAME VARCHAR(50);

DECLARE @DOB DATE;

DECLARE @MY_ID INT;

DECLARE @MEM_COUNT INT;

DECLARE @SL_NO INT;

SET @SL_NO=1;

DECLARE @ID INT;

SELECT @ID=ID FROM ( SELECT

ROW_NUMBER() OVER (ORDER BY ID ASC) AS ROWNUMBER,

ID

FROM @MY_CUR_SUB

) AS TEMP_TAB_SUB

WHERE ROWNUMBER = 1;

SELECT @NAME=NAME,@DOB=DOB FROM MY_DUMP WHERE ID =@ID;

SELECT @MY_ID=ISNULL(MAX(ID),0)+1 FROM MY_MAIN;

SELECT @MEM_COUNT=COUNT(ID) FROM MY_DUMP WHERE P_NO=@P_NO;

INSERT INTO MY_MAIN(P_NO,NAME,AGE,MY_ID,MEM_COUNT)

SELECT @P_NO,@NAME,DATEDIFF(YEAR,@DOB,GETDATE()),'ABCD-'+CAST(@MY_ID AS VARCHAR(10)),@MEM_COUNT;

WHILE (@MY_CUR_SUB_ROW_COUNT<=(SELECT COUNT(ID) FROM @MY_CUR_SUB))

BEGIN

--INSERT TO SUB

SELECT @ID=ID FROM ( SELECT

ROW_NUMBER() OVER (ORDER BY ID ASC) AS ROWNUMBER,

ID

FROM @MY_CUR_SUB

) AS TEMP_TAB_SUB

WHERE ROWNUMBER = @MY_CUR_SUB_ROW_COUNT;

SELECT @NAME=NAME,@DOB=DOB FROM MY_DUMP WHERE ID =@ID;

INSERT INTO MY_SUB(MY_MAIN_ID,NAME,AGE,MY_ID,SL_NO)

SELECT @MY_ID,@NAME,DATEDIFF(YEAR,@DOB,GETDATE()),'ABCD-'+CAST(@MY_ID AS VARCHAR(10))+'-'+CAST(@SL_NO AS VARCHAR(10)),@SL_NO;

SET @SL_NO=@SL_NO+1;

SET @MY_CUR_SUB_ROW_COUNT=@MY_CUR_SUB_ROW_COUNT+1;

END

SET @MY_CUR_ROW_COUNT=@MY_CUR_ROW_COUNT+1;

DELETE FROM @MY_CUR_SUB;

END

You Can Download the Working Code From here.