Traversing through the list of items in a table without using cursors.

What we do

1.  Retrieve the values from the Requirement Table.

2. Insert into the table variable.

3. Insert into the actual table by doing some processing on each record of table variable.

Structure of Table variable

DECLARE @RequirementIds TABLE
(
    Id INT,
    ReqId INT
)

--Retrieve the list of requirement ids
INSERT INTO @RequirementIds(Id,ReqId)
SELECT Id,CAST(Data AS INT) AS ReqId FROM Requirements

-- Get the total requirement ids
DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(Id) FROM @RequirementIds)



-- Declare an iterator
DECLARE @I INT
SET @I = 1



-- Loop through the rows of a table Assets
WHILE (@I <= @RowCount)
BEGIN     


        INSERT INTO Assets      

        (
          AssetsId,
          ProjectID,
          ReqID,
          AssetTypeID,       
          Title,
          [Description],
          FileSize,
          URL,
          CanVersion,
          [Version],
          [FileName],
          CreatedBy,
          DateCreated
         )       
        SELECT       
        (Select COALESCE(Max(AssetsId),0) + 1 FROM Assets WHERE ReqID=RI.ReqId AND ProjectID=@ProjectID) AS AssetsId,
        @ProjectID,
        RI.ReqId,
        @AssetTypeID,      
        @Title,
        @Description,
        @FileSize,
        REPLACE(@FURL,'{0}',CAST(RI.ReqId AS VARCHAR(3))) AS URL,
        @CanVersion,
        @Version,
        @FileName,   
        @CreatedBy,   
        @DateCreated
        FROM @RequirementIds RI WHERE Id = @I      
       
        SET @I = @I  + 1
END