Каким образом заполнить карточку контакта информацией о контрагенте.

--обновляет средсва связей и адреса контактов по их контрагентам.
--Детали адреса и средства связей контактов наполняются по их контрагентам, при этом старые записи остаются
-- если старые записи не нужны убрать/закомментировать строчку помеченную /*!!!*/
declare @NeedOldDetailRecords int
set @NeedOldDetailRecords = 0
set @NeedOldDetailRecords = 1   /*!!!*/


print 'Update tbl_Contact'
update t1
set     t1.Communication1=t2.Communication1,
    t1.Communication2=t2.Communication2,
    t1.Communication3=t2.Communication3,
    t1.Communication4=t2.Communication4,
    t1.Communication1TypeID=t2.Communication1TypeID,
    t1.Communication2TypeID=t2.Communication2TypeID,
    t1.Communication3TypeID=t2.Communication3TypeID,
    t1.Communication4TypeID=t2.Communication4TypeID,
    t1.TerritoryID    =t2.TerritoryID,
    t1.AddressTypeID =t2.AddressTypeID,
    t1.CountryID =t2.CountryID,
    t1.StateID =t2.StateID,
    t1.CityID =t2.CityID,
    t1.ZIP =t2.ZIP,
    t1.Address =t2.Address
from tbl_Contact t1, tbl_Account t2
where t1.AccountID is not null
and t2.id=t1.AccountID

if @NeedOldDetailRecords = 1
begin
    print 'update tbl_ContactCommunication'
    update tbl_ContactCommunication
    set position = null
    where exists(select * from tbl_Contact t2 where tbl_ContactCommunication.ContactID=t2.ID and t2.AccountID is not null)
   
    print 'update tbl_ContactAddress'
    update tbl_ContactAddress
    set IsPrimary = null
    where exists(select * from tbl_Contact t2 where tbl_ContactAddress.ContactID=t2.ID and t2.AccountID is not null)
end
if @NeedOldDetailRecords = 0
begin
    print 'delete from tbl_ContactAddress'
    delete tbl_ContactAddress
    from tbl_ContactAddress t1
    where exists(select * from tbl_Contact t2 where t1.ContactID=t2.ID and t2.AccountID is not null)
   
    print 'delete from tbl_ContactCommunication'
    delete tbl_ContactCommunication
    from tbl_ContactCommunication t1
    where exists(select * from tbl_Contact t2 where t1.ContactID=t2.ID and t2.AccountID is not null)
end

print 'Insert into tbl_ContactAddress'
insert into tbl_ContactAddress(id,ContactID,AddressTypeid,Address,CityID,StateID, Zip, CountryID, IsPrimary, TerritoryID)
select newid(), t1.id, t3.AddressTypeid, t3.Address, t3.CityID, t3.StateID, t3.Zip, t3.CountryID, t3.IsPrimary, t3.TerritoryID
from tbl_Contact t1, tbl_Account t2, tbl_AccountAddress t3
where t1.accountid is not null
and t1.accountid=t2.id
and t2.id=t3.accountid

print 'Insert into tbl_ContactCommunication'
insert into tbl_ContactCommunication(id,ContactID,Number,CommunicationTypeID,Position,Digits)
select newid(), t1.id, t3.Number, t3.CommunicationTypeID, t3.Position, t3.Digits
from tbl_Contact t1, tbl_Account t2, tbl_AccountCommunication t3
where t1.accountid is not null
and t1.accountid=t2.id
and t2.id=t3.accountid