Single Complex INSERT Or INSERT Plus UPDATE

Hello,

I am writing a stored procedure that will take data from several
different tables and will combine the data into a single table for our
data warehouse. It is mostly pretty straightforward stuff, but there is
one issue that I am not sure how to handle.

The resulting table has a column that is an ugly concatenation from
several columns in the source. I didn't design this and I can't hunt
down and kill the person who did, so that option is out. Here is a
simplified version of what I'm trying to do:

CREATE TABLE Source (
grp_id INT NOT NULL,
mbr_id DECIMAL(18, 0) NOT NULL,
birth_date DATETIME NULL,
gender_code CHAR(1) NOT NULL,
ssn CHAR(9) NOT NULL )
GO
ALTER TABLE Source
ADD CONSTRAINT PK_Source
PRIMARY KEY CLUSTERED (grp_id, mbr_id)
GO
CREATE TABLE Destination (
grp_id INT NOT NULL,
mbr_id DECIMAL(18, 0) NOT NULL,
birth_date DATETIME NULL,
gender_code CHAR(1) NOT NULL,
member_ssn CHAR(9) NOT NULL,
subscriber_ssn CHAR(9) NOT NULL )
GO
ALTER TABLE Destination
ADD CONSTRAINT PK_Destination
PRIMARY KEY CLUSTERED (grp_id, mbr_id)
GO

The member_ssn is the ssn for the row being imported. Each member also
has a subscriber (think of it as a parent-child kind of relationship)
where the first 9 characters of the mbr_id (as a zero-padded string)
match and the last two are "00". For example, given the following
mbr_id values:

12345678900
12345678901
12345678902
11111111100
22222222200

They would have the following subscribers:

mbr_id subscriber mbr_id
12345678900 12345678900
12345678901 12345678900
12345678902 12345678900
11111111100 11111111100
22222222200 22222222200

So, for the subscriber_ssn I need to find the subscriber using the
above rule and fill in that ssn.

I have a couple of ideas on how I might do this, but I'm wondering if
anyone has tackled a similar situation and how you solved it.

The current system does an insert with an additional column for the
subscriber mbr_id then it updates the table using that column to join
back to the source. I could also join the source to itself in the first
place to fill it in without the extra update, but I'm not sure if the
extra complexity of the insert statement would offset any gains from
putting it all into one statement. I plan to test that on Monday.

Thanks for any ideas that you might have.

-Tom.