Expanson on oprevious T-SQL help

I have been trying to expand on a query given to me to insert records from
one SQL2000 server table to a SQL2005 Server table. The query works like a
dream, however i would like to expand it slightly to become an UPDATE query
Orig Query
INSERT INTO
BossData.dbo.BacsHdrYearly
select * from
OPENDATASOURCE(
'SQLOLEDB',
'Data Source=SQL2000;User ID=@.USERNAME;Password=@.Password'
).BossData.dbo.BacsHdrYearly A
where not exists( select * from BossData.dbo.BacsHdrYearly
B where a.BHYear_LedgerKey =
b.BHYear_LedgerKey)
these are the fields in the table that need to be updated
BHYear_SubmissionType,
BHYear_FileStatus ,
BHYear_BACSTransmitted,
InvoiceNumber,
SubIndex
any pointers would be most helpful
Thank's in advancePeter
Let me understand your requiremnt. Could issue an UPDATE statement on
BacsHdrYearly now that INSERT INTO?
Do you want to UPDATE "directly" via OPENDATASOURCE command?
Can you create a linked server to SQL Server 2005 Server and use
UPDATE Table SET col=SERVER2000.dbname.dbo.tablename.col1............
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:36F3A117-2DC7-4CB2-BA77-6D9D3C80EA86@.microsoft.com...
>I have been trying to expand on a query given to me to insert records from
> one SQL2000 server table to a SQL2005 Server table. The query works like
> a
> dream, however i would like to expand it slightly to become an UPDATE
> query
>
> Orig Query
> INSERT INTO
> BossData.dbo.BacsHdrYearly
> select * from
> OPENDATASOURCE(
> 'SQLOLEDB',
> 'Data Source=SQL2000;User ID=@.USERNAME;Password=@.Password'
> ).BossData.dbo.BacsHdrYearly A
> where not exists( select * from BossData.dbo.BacsHdrYearly
> B where a.BHYear_LedgerKey =
> b.BHYear_LedgerKey)
> these are the fields in the table that need to be updated
> BHYear_SubmissionType,
> BHYear_FileStatus ,
> BHYear_BACSTransmitted,
> InvoiceNumber,
> SubIndex
> any pointers would be most helpful
> Thank's in advance|||rei,
i was given the INsetr statement which works fine to insert the data into my
dev sysetm, what i want to do is keep it updated. I have not created a linke
d
server, perfer to use opendatasource
"Uri Dimant" wrote:

> Peter
> Let me understand your requiremnt. Could issue an UPDATE statement on
> BacsHdrYearly now that INSERT INTO?
> Do you want to UPDATE "directly" via OPENDATASOURCE command?
> Can you create a linked server to SQL Server 2005 Server and use
> UPDATE Table SET col=SERVER2000.dbname.dbo.tablename.col1............
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
> news:36F3A117-2DC7-4CB2-BA77-6D9D3C80EA86@.microsoft.com...
>
>|||Ok, so what is wrong with linked server?
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:4B14FBAF-D3AC-4C80-9554-5A1F4D9FDFD4@.microsoft.com...
> rei,
> i was given the INsetr statement which works fine to insert the data into
> my
> dev sysetm, what i want to do is keep it updated. I have not created a
> linked
> server, perfer to use opendatasource
> "Uri Dimant" wrote:
>|||Did you try this. did it work. what is the error its giving?
update B set col1 = A.col1, col2 = a.col2
from BossData.dbo.BacsHdrYearly B,
OPENDATASOURCE(
'SQLOLEDB',
'Data Source=SQL2000;User ID=@.USERNAME;Password=@.Password'
).BossData.dbo.BacsHdrYearly A
where a.pkcol = b.pkcol