Hello I got a nice one.

I have 2 MS SQL 2000 databases on the same server both are almost alike.
Both have an items table with the field searchcode

I want to copy the searchcode from db1 table1 to db2 table1.

So something like this:

use [db1]
select itemcode,searchcode from table1
use [db2]
update [db2].[dbo].[table1] values ( select itemcode,searchcode from [db1].[dbo].[table1] )

Only this code doesn't work!!!!!!!!!!!!

The itemcodes are equal in both tables!

I want to schedule this to run every night.

This is just an example, but if I can do this, only 1 database needs to be maintained and the other database gets updated every night!

Anybody any ideas?

Thanx for any replies!

Ivan.

tuukie,
The syntax for the update statement is incorrect. Have a look in BOL to find the correct statement syntax.
You have the right idea with the [DB_Name].[dbo].table_name. I have used this technique a number of times.

To: RamyMahrous
Tried replication, can't do it because an extra field gets added to the table and the software who reads the table rejects that.
To:pclfw
Good to hear that I am on the right track, will have a look, when I find the solution I will post it

Got it all credits go to Denis from http://sqlservercode.blogspot.com/

update t2
set t2.searchcode = t1.searchcode
from [db1].[dbo].[table1] t1
join [db2].[dbo].[table1] t2 on t1.itemcode =t2.itemcode