Insert into 2 tables with cursor, then use returned scope_identity to insert into another table

Question

Basically I want to use an existing table, lets call it T1. I have to take that table, row by row and insert different columns into 2 separate tables. For example, C1, C2 into T2 and C3,C4 into T3.

During both of these inserts I need to make sure that the values that I am inserting do not already exist. Unfortunately there are multiple duplicates. Its not my data and it is very dirty. I have to do a ton of casting as is. Chances are good but not 100% that the column that I want to insert into T2 or T3 may exist while the other does not.

Once those inserts are done I need a @SCOPE_Identity or another way to uniquely identify and hold in two declared values the auto incremented ID's that T2 and T3 create.

These need to then be inserted into T4 which is a lookup table that mostly only stores FK, its own ID, a comment and a BIT.

I know it is a bit of a task, but I really need some help here. I have tinkered with multiple cursors and loops, but haven't got there yet. If I figure something out Ill post a solution, if nobody figures it out before me.

EDIT: So I worked it out. I have posted my code that has been made easy to read and use as an answer. If anyone wants to look at it, comment, make edits etc it will be there. There may be a better way to do it, so please comment if you can.

Answer 1

This is the user safe (I'll just call it that) version of what I eventually used to do my insert. This is really designed for importing data sets that in my eyes would be somewhat difficult to do otherwise without row level inserts. When I ran this it took roughly 2 minutes to insert 50, 000 rows. Bearing in mind that I had way more than 4 column, some columns were large, i had to cast everything at least once (some more than others), and I had to do various cutting using LEFT or RIGHT among other things to clean data for the new tables.

Declare @Col1 varchar(50);
DECLARE @Col2 varchar (50);
DECLARE @col3 varchar  (50);
DECLARE @col4 varchar  (50);
DECLARE @T2ID int;
DECLARE @T3ID int;


DECLARE Cur1 CURSOR -- Create the cursor
LOCAL FAST_FORWARD 
-- set the type of cursor. Note you could also use READ_ONLY and FORWARD_ONLY. 
-- You would have to performance test to see if you benifit from one or the other

FOR
--select FROM base table Table1
    SELECT
           Col1, Col2, Col3, Col4
           FROM 
           Table1
           WHERE Col1 IS NOT NULL AND Col3 IS NOT NULL  
           -- If the main columns are null then they are skipped. This was 
           -- required for my data but not necessarily yours.
           OPEN Cur1
           FETCH NEXT FROM Cur1 INTO   
           @Col1, @Col2, @Col3, @Col4;
           -- Assigns values to variables declared at the top


    WHILE @@FETCH_STATUS = 0
    BEGIN

                -- Select from table 2  
        SELECT @T2ID =  T2ID   
        -- where some data in the table is = to the stored data we are searching for
                FROM Table2
                WHERE @Col1 = [Col1]

                IF @@rowcount = 0
                    BEGIN
                        INSERT INTO T2
                            (Col1
                            ,Col2)
                        VALUES 
                            (@Col1
                            ,@Col2)

                        SET @T2ID = SCOPE_IDENTITY();
                    END;



    -- Selects from Table3      
        SELECT @Col3 =  Table3Col1  
                FROM Table3
                    IF @@rowcount = 0       
                    -- If no rows are returned then proceed with insert
                        BEGIN 
                            INSERT INTO Table3 
                                (col3
                                ,col4)
                            VALUES          
            -- Uses values assigned to the variables from the cursor select
                                (@col3
                                ,@col4)

                            SET @T3ID = SCOPE_IDENTITY();
                        END;


            -- Inserts the gathered row id's into the lookup table  
            INSERT INTO Table4
            (Table2ID
            ,Table3ID)
            VALUES (
            @Table2ID
           ,@Table3ID)

        FETCH NEXT FROM Cur1 INTO @Col1, @Col2, @col3, @col4; 


    END;

CLOSE Cur1;
DEALLOCATE Cur1;

If anyone has improvements to offer please do. I am open to suggestions. Also, unless someone wants me to I won't be accepting my answer as correct, as there may be a better answer.