Wherever CODE="BB", i wish to place a brand new row with the same row/column data just alter the CODE to "XX".

For instance:

Current Table:

ID |    CODE  | Name
---+----------+---------
 1 |    AA    | Tom
 2 |    BB    | Mary
 3 |    AA    | John
 4 |    CC    | Carlos
 5 |    CC    | Mario
 6 |    BB    | Steve
 7 |    AA    | Pablo

Same After SQL statement performed:

ID |    CODE  | Name
---+----------+---------
 1 |    AA    | Tom
 2 |    BB    | Mary
 3 |    AA    | John
 4 |    CC    | Carlos
 5 |    CC    | Mario
 6 |    BB    | Steve
 7 |    AA    | Pablo
 8 |    XX    | Mary
 9 |    XX    | Steve 

Is this completed in a SQL statement? instead of do things i would do that was produce a script to loop through each record checking for "BB" and placing a brand new row according to it's values.

This code features a check to make certain an 'XX' version does not already exist.

INSERT INTO CurrentTable
    (CODE, Name)
    SELECT 'XX', ct1.Name
        FROM CurrentTable ct1
        WHERE ct1.CODE = 'BB'
            AND NOT EXISTS(SELECT NULL
                               FROM CurrentTable ct2
                               WHERE ct1.name = ct2.name
                                   AND ct2.CODE = 'XX')

Presuming ID is definitely an IDENTITY column:

INSERT INTO myTable (Code, Name)
SELECT ('XX', Name) FROM myTable 
WHERE CODE ='BB'

(Not rerunnable...)

I'm not sure the way you generate id's but this is the way it's working:

insert into my_table 
select new_id() as id, 'xx' as Code, Name 
from my_table 
where Code='BB'