January 31, 2005 at 7:03 am
Apologies if this seems like just getting somebody else to do some work for me, but I have a problem I need to resolve and am having a hard time knowing how to go about it within query analyser.
I have 3 databases CRM, ACC_ESP and ACC_KBF (all on the same SQL Server)
I want to create a script that will cycle through each record within the CRM.Company table and do the following:
Check to see if a record in the ACC_ESP.SL_ACCOUNTS table exists with the ACC_ESP.SL_ACCOUNTS.CUCODE = CRM.Company.Comp_CompanyId.
If it does, then update the CRM.Company.Company_ESP = 1
Check to see if a record in the ACC_KBF.SL_ACCOUNTS table exists with the ACC_KBF.SL_ACCOUNTS.CUCODE = CRM.Company.Comp_CompanyId.
If it does, then update the CRM.Company.Company_KBF = 1
Then onto to the next record within the CRM.Company table and repeat.
The bad news is that the comp_companyid field is int 4 and the sl_accounts.cucode field is varchar 10.
I hope this makes sense!
Many thanks,
James Knight
January 31, 2005 at 10:06 am
fortunately I got some help from a friend, below seems to work well:
use crm
declare @company int
declare @ccount int
declare cur cursor for
select comp_companyid from company
FOR UPDATE OF comp_esp
open cur
fetch next from cur into @company
WHILE @@FETCH_STATUS = 0
BEGIN
set @ccount = (Select count(*) from ACC_ESP..SL_ACCOUNTS
where ACC_ESP..SL_ACCOUNTS.CUCODE = Convert(char(30),@company))
if (@ccount > 0)
Begin
update CRM..Company
set comp_esp = 1
WHERE CURRENT OF cur
end
fetch next from cur into @company
END
close cur
deallocate cur
I just ran it twice with the necessary changes for the different ACC_ databases.
February 1, 2005 at 9:35 am
You could have also used 'if exists' and 'select 1' instead of the 'select count(*)'.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply