Copy value from db1 table1 to db2 table1

[tuukie]

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.

[Ramy Mahrous]

Use replication

[pclfw]

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.

[tuukie]

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

[tuukie]

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