how to update a table from another table in differents db

Question

how to update a table from another table in differents db

Willian.Funes

Ten Centuries

Points: 1203
More actions
May 2, 2010 at 11:35 am

#234471

Hi experts,
I need your help, I have two tables in differents sql databases and I need to update the second one with the first one when a new record
is added, both tables have the same structure with the primary key (ItemC).
Im using a similar code for udatinf table2:
UPDATE db2.table2 a2 SET a2.ItemC = (SELECT p.ItemC FROM db1.table1 p WHERE p.ItemC = a2.ItemC)
Note: If the record exist in table2 I just need to update the record with new data, but if does not exist, I need to add the new record.
It is really urgent, I will appreciate any help.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Depending on your needs, you can use replication for this or you can use a trigger. I will also state that unless the tables/DB's are on different servers or unless the table is being written to a "history" table, it seems odd to want to update a table on one database to be exactly the same as another table in a different database.

To find out if a row exists in one table and not the other, you should probably check out what the EXCEPT clause does in Books Online.

If that bit of advice doesn't quite cut it for you, post back and we'll see if we can cut a code example for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Willian.Funes Ten Centuries Points: 1203 More actions · Answer 2

Thanks Jeff,

Both database are in the same server because they are using the same system but for different little companies in a corporation, so when users add a new product or price they must to add the same data in the other company and that's i need to do automaticly but this code is not working.

From DB2 Im using this code:

UPDATE db2.table2 a2 SET a2.ItemC = (SELECT p.ItemC FROM db1.table1 p WHERE p.ItemC = a2.ItemC)

¿Do you have any other idea to do that or sample code?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

I don't know what the best possible solution is, but i will try the following step:

1. Write a SP in Database1 that will check if a row is already existing; if yes, update data with the current values; If no, insert data into the destination table

2. Write a trigger on Table1 in Database1 for INSERT/UPDATE. Inside the trigger, i will call this SP.

This is just a experimental idea; I have not coded/tested it though. Will do it at some point of time and let you know!

Cheers!!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

willian.funes (5/3/2010)
Thanks Jeff,
Both database are in the same server because they are using the same system but for different little companies in a corporation, so when users add a new product or price they must to add the same data in the other company and that's i need to do automaticly but this code is not working.
From DB2 Im using this code:
UPDATE db2.table2 a2 SET a2.ItemC = (SELECT p.ItemC FROM db1.table1 p WHERE p.ItemC = a2.ItemC)
¿Do you have any other idea to do that or sample code?

Ah... first, this is a Microsoft SQL Server forum and there may be nuances of DB2 that I'm just not aware of. For example, UPDATE in SQL Server can actually use a FROM clause to join two tables instead of having to rely on correlated sub-queries to do the job. I don't believe that DB2 has such an enjoyment.

It's been about a bazillion years (I used to work with Oracle a very long time ago and it has the same problem, no FROM in UPDATE) since I've had to write a correlated subquery so I don't know if the following is actually 100% correct, but it should bring you closer. The problem with Oracle (and, I guess, DB2), is that you need the subquery to be repeated in the WHERE clause of the outer query something like this...

UPDATE db2.table2 a2

SET a2.ItemC = (SELECT p.ItemC FROM db1.table1 p WHERE p.ItemC = a2.ItemC)

WHERE a2.ItemC = (SELECT p.ItemC FROM db1.table1 p WHERE p.ItemC = a2.ItemC)

If DB2 has a MERGE statement (like the "newer" versions of Oracle), that would make the syntax a whole lot easier to understand because it will be very much like having an UPDATE with a FROM clause. I don't remember what the syntax for MERGE is but even if I did, it would probably be a bit different in DB2. Lookup MERGE in your DB2 help system and see if it's available.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Willian.Funes Ten Centuries Points: 1203 More actions · Answer 5

Thanks for reply Jeff,

Im working over SQL2005 infrastructure and not DB2, I got the solution with this code that I want to share for other users:

- DB1: is the database source

- TB1: is the data source

- DB2: is the Database to be updated

- TB2: is the table to be updated

---------------------------------

use DB2

go

update TB2 set Item1 = B.Item1,

Item2 = B.Item2,

Item3 = B.Item3

from DB2..TB2 as A, DB1..TB1 as B

WHERE A.Item1 = B.Item1

go

----------------------------------

Thanks in advance,

WFunes

Willian.Funes Ten Centuries Points: 1203 More actions · Answer 6

Thanks for reply Cheers,

I got the solution below in a single instruction because we needed to execute the process by demand, but your post using triggers is a valid solution..

Thanks in advance.

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 7

Having a central database that all of the companies access would solve this issue, wouldn't it? Might be the right way to go.

Thanks...Chris