SQL Cursor elimination

I need to migrate data in my application after introducing new table to db. I have prepared SQL script for that, but it uses cursor, which I want to elminiate due to performance issues (up to 2m of records in Configuration table).

What exactly I need to do is:

foreach config in Configuration where related_id is not null I need to migrate TypeA entity with id = related_id to TypeB; migrate all entities of type ElementA, which have reference to TypeA in column TypeA_id, to ElementB; also I need to update related_id on Configuration to newly created TypeB and delete migrated ElementA and TypeA entities.

DECLARE @RELATED INT,
    @CURRENT_CONFIG INT,
    @CREATED INT

DECLARE Crs SCROLL CURSOR FOR 
SELECT related_id, Id
FROM Configuration
WHERE related_id IS NOT NULL

OPEN Crs
FETCH NEXT FROM Crs INTO @RELATED, @CURRENT_CONFIG
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO TypeB(col)
    SELECT col
    FROM TypeA
    WHERE Id = @RELATED

    SET @CREATED = SCOPE_IDENTITY()

    UPDATE Configuration
    SET related_id = @CREATED
    WHERE Id = @CURRENT_CONFIG

    INSERT INTO ElementB (TypeB_id, Col1)
    SELECT @CREATED, Col1
    FROM ElementA
    WHERE TypeA_id = @RELATED

    DELETE ElementA
    WHERE TypeA_id = @RELATED

    DELETE TypeA
    WHERE Id = @RELATED

    FETCH NEXT FROM Crs INTO @RELATED, @CURRENT_CONFIG
END
DEALLOCATE Crs

Do you think it is possible to eliminate cursor in my case?

Solution

You need to use output clause here to get the inserted identity_col(idcol)

I assume the datatype of col as varchar(50).

        Create table #MyTable (idcol as int, col varchar(50));

            INSERT INTO TypeB
                        (col)
            OUTPUT INSERTED.identity_col --You need to relace identity_col with the actual identitycol.
                  ,INSERTED.col
                    INTO #MyTable
            SELECT col
            FROM   TypeA
            WHERE  Id IN(SELECT related_id
                         FROM   Configuration
                         WHERE  related_id IS NOT NULL)

--Add another column related_id in the temp table to get the original id

    ALTER TABLE #MyTable ADD related_id INT

    Update T
    set T.related_id=T1.id
    from #MyTable T
    JOIN TypeA T1
    on T.col= T1.col

            UPDATE A
            SET    a.related_id = B.idcol
            FROM   Configuration A
                    JOIN #MyTable B                
    ON B.related_id= A.related_id

        INSERT INTO ElementB (TypeB_id, Col1)
        SELECT distinct B.idcol, A.Col1
        FROM ElementA A 
        JOIN #MyTable B on A.TypeA_id= B.related_id

    DELETE ElementA
    WHERE  TypeA_id IN (SELECT distinct related_id
                        FROM   #MyTable)

    DELETE TypeA
    WHERE  Id IN (SELECT distinct related_id
                        FROM   #MyTable)