Cloning a record and dividing up its children between the two of them

I have two tables that look something like the following:

 Foo
 ---
 FooID  FooType
 -----  -------
 1      Red
 2      Red
 3      Green
 4      Red
 5      Blue
 6      Red

 Bar
 ---    
 BarID  BarFooID  BarData
 -----  --------  -------
 1      1         A
 2      1         B
 3      2         C
 4      2         D
 5      3         E
 6      3         F
 7      3         G
 8      3         H

BarID's 6 and 7 were mistakenly associated with FooID3 and should have been associated with a different Foo, with a different Foo Type. I have BarID-6 & 7 listed in a separate table (BadBar)

What I'd like to do is copy FooID-3 to a new record (FooID-7) and then repoint BarID-6 & 7's BarFooID at FooID-7, and then update FooID-7's FooType to its new value.

My expected out put would look something like this:

 Foo
 ---
 FooID  FooType
 -----  -------
 1      Red
 2      Red
 3   开发者_StackOverflow中文版   Green
 4      Red
 5      Blue
 6      Red
 7      Purple     // new row

 Bar
 ---    
 BarID  BarFooID  BarData
 -----  --------  -------
 1      1         A
 2      1         B
 3      2         C
 4      2         D
 5      3         E
 6      7         F        // updated
 7      7         G        // updated
 8      3         H

I can imagine how to do this in pseudo code:

For Each Bar in BadBars
    copy Bar's Foo to a new Foo
    remember the new Foo's FooID
    update the new Foo's FooType
    update Bar's BarFooID to the remembered FooID
End For

Is there a way I can create an SQL transaction to do this as one operation or at least clone the Foo and relink the Bar to the clone (I can always make a second pass to update the clones).

Or am I stuck writing a one off script for this?

Table setup using table variable for easy testing. I assume that FooID and BarID is identity columns.

declare @Foo table 
(
  FooID int identity primary key,
  FooType varchar(10)
)

declare @Bar table 
(
  BarID int identity primary key,
  BarFooID int,
  BarData char(1)
)
declare @BadBar table 
(
  BarID int
)

Insert sample data. This syntax works in SQL Server 2008:

insert into @Foo values
('Red'),
('Red'),
('Green'),
('Red'),
('Blue'),
('Red')

insert into @Bar values
(1, 'A'),
(1, 'B'),
(2, 'C'),
(2, 'D'),
(3, 'E'),
(3, 'F'),
(3, 'G'),
(3, 'H')

insert into @BadBar values
(6),
(7)

The script:

-- Variable to hold the the new FooID
declare @NewFooID int

-- Copy FooID 3 to new Foo row
insert into @Foo (FooType)
select FooType
from @Foo
where FooID = 3

-- Capture the new auto created FooID
set @NewFooID = scope_identity()

-- Update BarFooID in Bar for all BarID in BadBar with new FooID
update B
set BarFooID = @NewFooID
from @Bar as B
  inner join @BadBar as BB
    on B.BarID = BB.BarID

-- Change FooType for newly created Foo
-- This step is not really necessary because you could
-- use 'Purple' in the insert statement above instead
update @Foo
set FooType = 'Purple'
where FooID = @NewFooID