Hi

I am trying to write one cursor which is been made from join of two tables.
and then i want to update one table by using where current of cursor.
Can i do that..?

or should i create the view by joining the two tables and then try to update it.

or should i create the temp table and put the records in that and then try to update that.

Please guide me in this.

Thanks,
Harish
====



CREATE PROCEDURE SP_P4
AS
declare c123 cursor for
select

COMPANY.COMPANY,
COMPANY.COMPANYNAME,
COMPANY.CUSTOMERNUMBER,
COMPANY.FEDTAXID,
COMPANY.DUNSNUMBER,
COMPANY.S1099,
COMPANY.DUNSSUFFIX,
COMPANYADDRESS.ADDRESSSEQ,
COMPANYADDRESS.COUNTRY,
COMPANYADDRESS.ADDRESSTYPE,
COMPANYADDRESS.ADDRESS1,
COMPANYADDRESS.ADDRESS2,
COMPANYADDRESS.ADDRESS3,
COMPANYADDRESS.ADDRESS4,
COMPANYADDRESS.CITY,
COMPANYADDRESS.STATE,
COMPANYADDRESS.ZIP

from COMPANY,COMPANYADDRESS
where company.company=COMPANYADDRESS.company
order by company.company
declare @company char
declare @COMPANYNAME char
declare @CUSTOMERNUMBER char
declare @FEDTAXID char
declare @DUNSNUMBER char
declare @S1099 char
declare @DUNSSUFFIX char
declare @ADDRESSSEQ int
declare @COUNTRY char
declare @ADDRESSTYPE char
declare @ADDRESS1 char
declare @ADDRESS2 char
declare @ADDRESS3 char
declare @ADDRESS4 char
declare @CITY char
declare @STATE char
declare @ZIP char

open c123
fetch next from c123 into @company,@COMPANYNAME,@CUSTOMERNUMBER,@FEDTAXID,@D UNSNUMBER,@S1099,
@DUNSSUFFIX,@ADDRESSSEQ,@COUNTRY,@ADDRESSTYPE,@ADD RESS1,@ADDRESS2,@ADDRESS3,@ADDRESS4,@CITY,
@STATE,@ZIP

while @@fetch_status=0

begin
update companyaddress set ADDRESSSEQ=@ADDRESSSEQ where current of c123
update companyaddress set COUNTRY=@COUNTRY where current of c123
update companyaddress set ADDRESSTYPE=@ADDRESSTYPE where current of c123
update companyaddress set ADDRESS1=@ADDRESS1 where current of c123
update companyaddress set ADDRESS2=@ADDRESS2 where current of c123
update companyaddress set ADDRESS3=@ADDRESS3 where current of c123
update companyaddress set ADDRESS4=@ADDRESS4 where current of c123
update companyaddress set CITY=@CITY where current of c123
update companyaddress set STATE=@STATE where current of c123
update companyaddress set ZIP=@ZIP where current of c123

fetch next from c123 into @company,@COMPANYNAME,@CUSTOMERNUMBER,@FEDTAXID,@D UNSNUMBER,@S1099,
@DUNSSUFFIX,@ADDRESSSEQ,@COUNTRY,@ADDRESSTYPE,@ADD RESS1,@ADDRESS2,@ADDRESS3,@ADDRESS4,@CITY,
@STATE,@ZIP

end

close c123
deallocate c123


declare @v datetime
declare @filename varchar(32)
select @v=getdate()
select @filename = 'XAPIALTMKT'+CONVERT(varchar, @v, 112)+ SUBSTRING(CONVERT(varchar, @v, 108), 1, 2)
+ SUBSTRING(CONVERT(varchar, @v, 108), 4, 2)+ SUBSTRING(CONVERT(varchar, @v, 108), 7, 2) + '.TXT'
declare @cmdstring varchar(255)
Select @cmdstring = 'bcp conversion..expt1 out d: est' + @filename + ' -c -t"|" -r
-S -Usa -Psa'

exec master..xp_cmdshell @cmdstring
drop table expt1