insert into members (SSN, lname, fname, phone, address, city, state, zip) 
select au_id, au_lname, au_fname, phone, address, city, state, zip
from pubs.dbo.authors
go

insert groups values('00001', 'fishing');
insert groups values('00002', 'hiking');
insert groups values('00003', 'biking');
insert groups values('00004', 'climbing');
insert groups values('00005', 'sailing');
insert groups values('00006', 'dancing');
insert groups values('00007', 'toastmasters');
insert groups values('00008', 'camping');
insert groups values('00009', 'diving');
insert groups values('00010', 'skating');

go

update members
set groupid = groups.gid
from members, groups
where right(gid, 1) = left(SSN, 1) 
go