Добрый день.
Иногда перед нами стоит задача заполнить карточки контактов информацией о контрагентах (адрес, средства связи), которые указаны в их карточках в поле Контрагент. Для того, чтобы Выполнить этой действие Вам необходимо выполнить скрипт на базе данных:
--обновляет средсва связей и адреса контактов по их контрагентам.
--Детали адреса и средства связей контактов наполняются по их контрагентам, при этом старые записи остаются
-- если старые записи не нужны убрать/закомментировать строчку помеченную /*!!!*/
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
